Module
 

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

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

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

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 wrote:

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


--

Dave Peterson

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

Monty

"Dave Peterson" wrote:

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 wrote:

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


--

Dave Peterson