Module

[Monty]

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]

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]

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]

Do you want to change all the worksheets in the workbook--or just a few:

All the worksheets:

Option Explicit
Sub testme1()

Dim wks As Worksheet
Dim myDate As Date

myDate = DateSerial(Year(Date), Month(Date) - 1, 1)

For Each wks In ActiveWorkbook.Worksheets
With wks.PageSetup
.CenterFooter = Format(myDate, "MMMM") & " Profile"
End With
Next wks

End Sub

Just a few--you supply the names:

Option Explicit
Sub testme2()

Dim mySheetNames As Variant
Dim myDate As Date
Dim sCtr As Long

myDate = DateSerial(Year(Date), Month(Date) - 1, 1)
mySheetNames = Array("sheet1", "sheet2", "sheet3")

For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
With Worksheets(mySheetNames(sCtr)).PageSetup
.CenterFooter = Format(myDate, "MMMM") & " Profile"
End With
Next sCtr

End Sub

 

[Monty]

This worked a treat thanks once again for all your help.

Monty