Sheet settings

[Colin]

Hi there,

I ahev recorded a macro that allows me to alter the set-up
of a sheet as follows:

Center the sheet horizontally and vertically (for printing)
Includes Row and Column headings (for printout)
Fits sheet to 1 page wide by 1 page tall (for printout)
Makes sheet landscape (for printout)
Makes the left and right margins 0.9 inches each (for
printout)
Adjusts zoom size to 85%
Remove gridlines
Include header, on the left, that has the tab name of the
sheet in Arial, 14 point, bold.

How can I adjust this macro so that whenever I open a
workbook (whether it has been worked on previously or if
its a new workbook), I can run this macro, and every sheet
in the workbook will have these settings (i.e. some sort
of "for each sheet in workbook, do....")

Many Thanks,

Colin

 

[Bob Phillips]

Colin,

You can do it with application events. To do this, you will need to have a
workbook that opens when Excel starts that set it all up, say Personal.xls.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

'your code or a call to your macro

End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)