I have a workbook with 20 to 30 worksheets in it. Based on the eventual use of the printout, I need to change the footer on each page. In the past I've done a For Each Sht in Thisworkbook.Sheets and changed the page setup individually. This is REALLY SLOW. Manually, I can select all the worksheets and then change the footer in page setup and it will hit all the worksheets, but I can't get this method to work in code... it only changes the active sheet.
Anybody have any suggestions? Thanks!
Fast Method Of Changing Footers on Multiple Worksheets? [SOL
-
-
-
Try this, I haven't fully tested it yet but it should do what you want
-
Thanks RoyUK, your solution is very similar to what I have been using (see below):
For Each Sht In ThisWorkbook.Sheets
Sht.PageSetup.RightFooter = "Message"
Nextand is about the same speed unfortunately. The visible status of a sheet does not seem to affect the code above. I very much appreciate your response. I should have posted this code in my original one.
-
Hi,
One way You might try and play around with is the following:
Sub Group_Mode()
Sheets(Array("Sheet1", "Sheet2")).SelectWorksheets("Sheet1").Activate
With ActiveSheet.PageSetup
.RightFooter = "Message"
End WithEnd Sub
This setting effect all the selected sheets in one go
-
Thanks for the Reply XL-Dennis. I had tried something similar:
Sheets.Select
then the same as yours...But, the problem is that when this is done in code it only changes the active sheet and not the others in the Array or Group. When you do this manually, it works just fine. The difference is very frustrating!
-
-
HI,
Well, it works for me so a) what version of XL do You run and b) hwo does You code look for it?
-
I run XL97... so that could very well be the problem. I am unsure about what you mean on your second question. The entire section of the code I tried (and it only changes the active sheet) is:
Sub ChngAllFtrs()
Sheets.Select
ActiveSheet.PageSetup.RightFooter = "Message"
End Sub -
Hi,
I ran into the same problem only the other day. Have a look at this post and see if the suggestions that were made help you:
http://216.92.17.166/board2/viewtopic.php?t=46709&highlight=
HTH
-
Hi,
Quote
I run XL97... so that could very well be the problem.Yes, unfortunately it looks like that...
In 2000/2002/2003 it runs well....
Go with Richie's suggestion :cheers:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!