How do I insert the name of a named range in a footer in Excel?

  • Thread starter Thread starter AF
  • Start date Start date
A

AF

Hello,

I would like to have the name of a named range appear in a footer in Excel.
How can I do this automatically, without having to actually type in the name
of the range in the footer field, so that it can apply to multiple worksheets
that each have one named range in them? I've found posting on how to get a
cell value or cell reference to appear in the footer, but haven't found
anything regarding named ranges.

I think this needs to be done in VBA, but my VBA knowledge is very limited.

Thank you!

AF
 
Here is an attempt. Ranges are stored at the workbook level, so I
think you'll need to check every range for every sheet to see if the
address of the named range (RefersToLocal) matches the name of the
sheet. Usually RefersToLocal will be something like =Sheet1!$A$1:$D$9



Public Sub testfoot()
Dim sht As Worksheet, wkb As Workbook
Set wkb = ThisWorkbook
Dim nmeRange As Name, sRange As String
For Each sht In wkb.Sheets
sRange = ""
For Each nmeRange In wkb.Names
If InStr(1, nmeRange.RefersToLocal, sht.Name) <> 0 Then
sRange = nmeRange.Name
End If
Next nmeRange
sht.PageSetup.CenterFooter = sRange
Next sht
Set wkb = Nothing
End Sub
 
Back
Top