Macro to print worksheet specific named range in footer for that worksheet

[Ana_P]

Hello,

I'm still a beginner with VBA/macros but I've done my head in trying to work out the following two questions on my own so anyone's help would be greatly appreciated.

I have a workbook with a number of worksheets, many of which have a worksheet specific named range for a cell called "WS_Ref".

My first issue is that I need the worksheet specific "WS_Ref" to print on the footer of the worksheet that it's for. The way the code is at the moment prints the Active Worksheet's "WS_Ref" on all of the footers; however I want the footer of each worksheet to contain it's own "WS_Ref". I have tried using ws.Range however I get the following: "Runtime error '1004': Method 'Range' of object '_Worksheet' failed". The code I have is as follows (with the part I'm having issues with in red font):

[COLOR=#333333]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Workbook_BeforePrint(Cancel As Boolean)
  
    'Add info to each sheets' header and footer
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("A6").Value = "Income & Tax Summary" Or ws.Range("A6").Value = "Individual Tax Summary" Or ws.Range("A6").Value = "Tax Reconciliation (Company)" Or ws.Range("A6").Value = "Tax Reconciliation (Trust, Partnership)" Or ws.Range("A6").Value = "Tax Reconciliation (Sole Trader)" Then
            ws.PageSetup.LeftHeader = ""
            ws.PageSetup.LeftFooter = ""
            ws.PageSetup.CenterFooter = "&""Calibri""&I&8Liability limited by a scheme approved under Professional Standards Legislation"
            ws.PageSetup.RightFooter = ""
        ElseIf ws.Range("A6").Value = "Workpaper Index" Or ws.Range("A6").Value = "Work In Office Checklist" Then
            ws.PageSetup.LeftHeader = ""
            ws.PageSetup.LeftFooter = ""
            ws.PageSetup.CenterFooter = ""
            ws.PageSetup.RightFooter = ""
        ElseIf ws.Range("A6").Value = "Queries" Or ws.Range("A6").Value = "Review Points" Or ws.Range("A6").Value = "Issues for Next Year" Then
            ws.PageSetup.LeftHeader = ""
            ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("B2").Value & "/" & "&F"
            ws.PageSetup.CenterFooter = "&""Calibri""&I&8Liability limited by a scheme approved under Professional Standards Legislation"
            ws.PageSetup.RightFooter = "&""Calibri""&8&A"
        ElseIf ws.Range("A6").Value = "Journal Entries" Or ws.Range("A6").Value = "Adjusting Journal" Then
            ws.PageSetup.LeftHeader = "&""Calibri""&B&14" & Range("B1").Value
            ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("B2").Value & "/" & "&F" & "/" & "&A"
            ws.PageSetup.CenterFooter = ""
            ws.PageSetup.RightFooter = "&""Calibri""&10&Page &P"
        Else
            ws.PageSetup.LeftHeader = "&""Calibri""&B&14" & Range("B1").Value
            ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("B2").Value & Chr(10) & "&F" & Chr(10) & "&A"
            ws.PageSetup.CenterFooter = "&""Calibri""&I&8Liability limited by a scheme approved under Professional Standards Legislation"
            ws.PageSetup.RightFooter = "&8Prepared by: " & Range("Prepared_By").Value & Chr(10) & _
                                        "Reviewed by: " & Range("Reviewed_By").Value & Chr(10) & _
                                        "Ref:   " & [COLOR=#FF0000]Range("WS_Ref").Value[/COLOR]
        End If
    Next ws
End Sub</code>[/COLOR]

My second issue with the above macro is that there are approx. 40 worksheets in the workbook so when choosing to print a worksheet it takes a while before the code runs and does all the above before printing the worksheet needed. Is there any way to fix/change this above code so there isn't this lag each time a worksheet needs to be printed?

If I've not made my issues clear enough please let me know.

If anyone has any suggestions, you'll save my day as I have searched the internet far and wide!

Thanks

 

[Ana_P]

I have just realised there is something a little off with this in that when the Workbook_Open and updateHeader runs, the code uses Range A1 rather than B1 in the left header. This only corrects itself once the Workbook_BeforePrint even is run. As mentioned in a previous post, the idea behind the macro I created is that all the worksheets in the workbook are updated with the respective header/footer (so when print-previewing all the information is there). Instead of showing the client's name (value in B2) in the header it is currently showing "Client:" (value in B1). I believe the same thing was happening with the left footer also (showing "Code:" instead of the client's code). Is there any way to fix this?

Thanks!

Sorry just realised where the issue is stemming from! It turns out it is pulling the ws.Range("B1").Value and ws.Range("B2").Value from the Index page as opposed to the actual page it is updating! Is there a way to fix the coding so it uses the range from the page that it is updating the header/footer on?

 

[shknbk2]

from the Index page as opposed to the actual page it is updating

I don't understand how the index page is different from the updated page. "ws" is the sheet that the header/footer is being updated, and cells B1 and B2 on that page are used. If you're not wanting to update the header/footer on ws, what page are you trying to update?

 

[Ana_P]

If you're not wanting to update the header/footer on ws, what page are you trying to update?

I am trying to update ws however on the workbook_open event it uses the values of the Index page (which aren't in the same columns as the rest of the worksheets), however on the before_print event the same code pulls correctly from the ws it is updating. That's why I'm confused coz it's the same code it's running on both events!

 

[shknbk2]

I think I understand you now. That is really weird. I can't see why it would be grabbing the data from the Index sheet when updating other worksheets as part of the Workbook_Open event. Do you know how to step through the code to debug? Put a breakpoint on the "updateHeader ws" line in the Open event and step through the code as it updates each page. Maybe that will show something we're missing.

I also just happened to think of something. Before you do the debugging, what happens if you manually run the Open code after the workbook is open? Put the cursor somewhere in the code and hit F5. Does it update all sheets okay once the workbook is already open? If so, maybe there's something going on during the opening that makes it do strange things. If it still does the same thing after manually running it, do the debugging above.

 

[Ana_P]

Maybe that will show something we're missing.

Did all the running/debugging etc but couldn't work it out from that. Turns out it was a simple fix - in the coding some lines I had just "Range.("B2").Value and others I had "ws.Range.("B1").Value. I added "ws." at the beginning of all my Range references and it appears to be working correctly now . The reason the before_print even was working correctly is because it was set as UpdateHeaderFooter ActiveSheet (thereby pulling the data from the ActiveSheet) whereas the Workbook_open event was using UpdateHeaderFooter ws (and ActiveSheet is the Index).

Thanks heaps from helping me get to the bottom of this! Having someone to help step you through different ways to get to the bottom of something really helps!

 

[shknbk2]

No problem. I tried looking through the last bit of code in this thread and couldn't find a Range without a ws.Range. That's why I was having trouble isolating the issue. Glad you could solve it.

 

[Ana_P]

Hello again

Can anyone give me a suggestion on how to much the following code less cumbersome and not as complicated? Basically, I need the headers/footers on certain worksheets to update with certain named ranges, and others worksheets I don't need it to do anything - however there are a greater number of worksheets (the 'Else' part of the code) that need the header/footer updated then the ones that don't. And the 'Else' part of the code also covers any additional worksheets added by the user. The reason I'm using ws.Range("A6").Value as this cell doesn't change (it is the original sheet tab name) on all worksheets regardless of how many copies of the worksheet there will be. I'm still a beginner with VBA and can't quite work out which would be the best way to go about it - it takes too long to run in it's current format!

Sub UpdateHeaderFooter(ws As Worksheet)
    Application.ScreenUpdating = False
    Dim ref As String
        On Error Resume Next
        ref = ws.Range("WS_Ref").Value
        On Error GoTo 0
    If ws.Range("A6").Value = "Income & Tax Summary" Or ws.Range("A6").Value = "Individual Tax Summary" Or _
            ws.Range("A6").Value = "Tax Reconciliation (Company)" Or ws.Range("A6").Value = "Tax Reconciliation (Trust, Partnership)" Or _
            ws.Range("A6").Value = "Tax Reconciliation (Sole Trader)" Then
        ws.PageSetup.LeftHeader = ""
        ws.PageSetup.LeftFooter = ""
        ws.PageSetup.CenterFooter = ""
        ws.PageSetup.RightFooter = ""
    ElseIf ws.Name = "Index" Or ws.Name = "Table of Contents" Or ws.Name = "Rates" Or ws.Name = "Named Ranges" Or ws.Name = "CPI Rates" Or _
            ws.Range("A6").Value = "Workpaper Index (Accounting)" Or ws.Range("A6").Value = "Workpaper Index (Individual)" Or _
            ws.Range("A6").Value = "Work In Office Checklist" Or ws.Range("A6").Value = "Collation Checklist (Group)" Or _
            ws.Range("A6").Value = "Collation Checklist (Individual)" Or ws.Range("A6").Value = "Signed Documents Checklist" Then
        ws.PageSetup.LeftHeader = ""
        ws.PageSetup.LeftFooter = ""
        ws.PageSetup.CenterFooter = ""
        ws.PageSetup.RightFooter = ""
    ElseIf ws.Range("A6").Value = "Trial Balance" Or ws.Range("A6").Value = "SOL6 Trial Balance" Then
        ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("Client_Code").Value & "/" & "&F" & "/" & "&A"
        ws.PageSetup.RightFooter = "&""Calibri""&10Page &P"
    ElseIf ws.Range("A6").Value = "Accounts Checklist" Or ws.Range("A6").Value = "Tax Checklist (Company)" Or ws.Range("A6").Value = "Tax Checklist (Trust)" Or _
            ws.Range("A6").Value = "Tax Checklist (Partnership)" Or ws.Range("A6").Value = "Tax Checklist (Individual)" Or ws.Range("A6").Value = "BAS Checklist" Or _
            ws.Range("A6").Value = "Post-Review Checklist (Accounting)" Or ws.Range("A6").Value = "Post-Review Checklist (Individual)" Then
        ws.PageSetup.LeftHeader = ""
        ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("Client_Code").Value & "/" & "&F" & "/" & "&A"
        ws.PageSetup.CenterFooter = ""
        ws.PageSetup.RightFooter = "&8Prepared by: " & Range("Prepared_By").Value & Chr(10) & _
                                    "Reviewed by: " & Range("Reviewed_By").Value & Chr(10) & _
                                    "Ref:   " & ref
    ElseIf ws.Range("A6").Value = "Queries" Or ws.Range("A6").Value = "Issues for Next Year" Then
        ws.PageSetup.LeftHeader = ""
        ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("Client_Code").Value & "/" & "&F"
        ws.PageSetup.CenterFooter = ""
        ws.PageSetup.RightFooter = "&""Calibri""&8&A"
    ElseIf ws.Range("A6").Value = "Journal Entries" Or ws.Range("A6").Value = "Adjusting Journal SOL6" Or ws.Range("A6").Value = "Adjusting Journal" Then
        ws.PageSetup.LeftHeader = "&""Calibri""&B&14" & Range("Client_Name").Value
        ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("Client_Code").Value & "/" & "&F" & "/" & "&A"
        ws.PageSetup.CenterFooter = ""
        ws.PageSetup.RightFooter = "&""Calibri""&10Page &P"
    Else
        ws.PageSetup.LeftHeader = "&""Calibri""&B&14" & Range("Client_Name").Value
        ws.PageSetup.LeftFooter = "&""Calibri""&8" & Range("Client_Code").Value & Chr(10) & "&F" & Chr(10) & "&A"
        ws.PageSetup.CenterFooter = ""
        ws.PageSetup.RightFooter = "&8Prepared by: " & Range("Prepared_By").Value & Chr(10) & _
                                    "Reviewed by: " & Range("Reviewed_By").Value & Chr(10) & _
                                    "Ref:   " & ref
    End If
    Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated!

Thanks
Ana

 

[Ana_P]

How to make rather than how to much