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, a few of which have a worksheet specific named range for a cell called "WS-Ref". My first issue is that I want this worksheet reference 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 each footer 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):

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:   " & Range("WS_Ref").Value
        End If
    Next ws
End Sub
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?

Again, all help will be greatly appreciated!!

Thanks