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

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)

"Colin" wrote in message
...
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