Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Module Protection Programatically | Excel Discussion (Misc queries) | |||
error "module not found" | Excel Discussion (Misc queries) | |||
Module updating of Protected Cells | Excel Discussion (Misc queries) | |||
Difference in the listing of the New module addition in 2002 version | Excel Discussion (Misc queries) | |||
Access Module coded converted to Excel Function | Excel Discussion (Misc queries) |