I've inherited a workbook with a macro code that when executed
modifies each worksheet's footers. Below is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("inpQuoteNum").Address Or Target.Address
= Range("inpGrpName").Address Then
Dim lf As String, cf As String, rf As String
lf = "Group Name: " & Range("inpGrpName").Value
cf = "&P"
rf = "Quote # " & Range("inpQuoteNum").Value
For Each ws In Worksheets
With ws.PageSetup
.LeftFooter = lf
.CenterFooter = cf
.RightFooter = rf
End With
Next ws
End If
End Sub
With all the looping and the number of sheets in the workbook,
execution is a bit slow. I thought that if you could simply group the
workbook sheets that needed to be changed and then change them, the
execution would be quicker. However, after macro recording the steps,
and then editing the steps to specifically change only that part of
the worksheets, I find that only one sheet is changed. The following
is my code:
' mcrChangeFooters Macro
Macro recorded 10/4/2010
'
'
Sheets(Array("Cover Sheet", "Census", "RMM - Debits", "Rate
Sheets", _
"Standard Pairings", "Base Rate Calculation", "Age-Gender
Development", _
"Area Factor", "RAF", "RMM - Short Form", "RMM - Employer
Form", _
"Group Information")).Select
With ActiveSheet.PageSetup
.LeftFooter = "Group Name: test"
.RightFooter = "Quote # aaaaa"
End With
Sheets("Start").Select
End Sub
I understand that I will have to declare variables and populate the
variables for the left footer and the right footer, but could someone
review and let me know how to write the code so it works, or show me
some code that would run excel more quickly than the code I inherited
or I created.
Thanks in advance for your help!
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
