I know that this is probably a very simple issue but I can't find an answer
and everything that I have tried doesn't work. How can I modify this code so
I can either use Excel's "Range Names" ie LftHdrTxt which refers to cell B11
on Sheet2, or ranges Sheet2.Range("B11"). Currently the text LftHdrTxt is
placed in the left header and CtrHdrTxt in the center header etc. The quotes
are required. The PageSetupXl4m defines LeftHead As String with the
following "Optional LeftHead As String," etc. I'm trying to update the
Headers and Footers on 75 sheets from values input by a user in cells, on a
single sheet, in an excel template. Because of different page setup's on
many of the sheets ie portrait on some and landscape on Others. Because of
this I can't simply activate all of the sheets then enter the desired
information in page setup. I'm trying to modify code I found on the
McGimpsey & Associates website. www.mcgimpsey.com/excel/udfs/pagesetup.html.

PageSetupXL4M LeftHead:="LftHdrTxt", CenterHead:="CtrHdrTxt",
RightHead:="RhtHdrTxt", LeftFoot:="LftFtrTxt", CenterFoot:="CtrFtrTxt",
RightFoot:="RhtFtrTxt"

Here is the code for PageSetupXL4M

Public Sub PageSetupXL4M( _
Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & LeftHead
If CenterHead < "" Then head = head & "&C" & CenterHead
If RightHead < "" Then head = head & "&R" & RightHead
If Not head="" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
If RightFoot < "" Then foot = foot & "&R" & RightFoot
If Not foot="" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub