Dave

Thanks for this the first one works a treat, however I have 26 sheets in the
workbook and I tried adapting it for each one like this:-

With Worksheets("sheet1", €œsheet2€�, €œsheet3€�).PageSetup

However this did not work and I have had to use the long process (see below)
which is as you can see very long, is there any shorter way.
Once again thanks for your help.

Option Explicit
Sub testme()

Dim myDate As Date
myDate = DateSerial(Year(Date), Month(Date) - 1, 1)
With Worksheets("sheet1").PageSetup
.CenterFooter = Format(myDate, "MMMM") & " Profile"
End With

myDate = DateSerial(Year(Date), Month(Date) - 1, 1)
With Worksheets("sheet2").PageSetup
.CenterFooter = Format(myDate, "MMMM") & " Profile"
End With

myDate = DateSerial(Year(Date), Month(Date) - 1, 1)
With Worksheets("sheet3").PageSetup
.CenterFooter = Format(myDate, "MMMM") & " Profile"
End With
End Sub


"Dave Peterson" wrote:

You could figure out the date like this:

Option Explicit
Sub testme()

Dim myDate As Date
myDate = DateSerial(Year(Date), Month(Date) - 1, 1)
With Worksheets("sheet1").PageSetup
.LeftFooter = Format(myDate, "MMMM") & " Profile"
End With
End Sub

And if you're using xl2k or higher, you could use something like:

Option Explicit
Sub testme()
With Worksheets("sheet1").PageSetup
.LeftFooter = MonthName(Month(Date) - 1, abbreviate:=False) _
& " Profile"
End With
End Sub


Monty wrote:

I have the following module working within a spreadsheet and it works
perfectly. Is there anyway I can add the January Profile, February Profile
etc on the footer when I run this module. One more thing the Profile are run
one month behind so when I run this report say today 17th May the footer
would read April Profile. Thanks for any help.

Private Sub CommandButton1_Click()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations summary", "RCA incl
RIM", "First Qtr", "Second Qtr", "Third Qtr", "Fourth Qtr")).Delete
Application.DisplayAlerts = True

For Each Sh In wb.Worksheets
Sh.Columns("A:B").EntireColumn.Delete
Next
End Sub

thanks

Monty

--

Dave Peterson