Input Box to Add Information into Page Header

[sanityendshere]

I have been making macros for a specific table format, which will be used for multiple sites in a particular project. Is it possible to use an input message box to add the site name into the page header? If so, how do I properly reference the input box information in the center header section? I tried putting MyInput where I currently have Site Name but that did not work. Thanks in advance for any help you can provide.

Sub InsertHeaderFooter()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Variables

Dim ws As Worksheet
Dim WS_Count As Integer
Dim i As Integer, j As Integer, k As Integer, LastColNum As Integer
Dim LastColName As String
Dim MyInput As Variant
Dim CellRange As Range 'for later on when you want to dynamically define and offset your range
' Set WS_Count equal to the number of worksheets in the active workbook ---------------------------------------------------------------------------

WS_Count = ActiveWorkbook.Worksheets.Count

' Use a msgbox to allow the User to add the site ID to the header

MyInput = InputBox("Site Name", "Please enter Site Name", "Site Name")

' Begin the loop.


For j = 1 To WS_Count
'Set/initialize the variables here that are specific to the sheet, so each time we move to a new sheet they update-------------------------
Set CellRange = Sheets(j).Range("C4") 'use this for your offsetting, to tailor references to the column count
i = 1 'Start at zero, the first instance of it being non-null will return 1 since it immediately adds one
Do Until CellRange.Cells(1, i + 1) = "" 'i+1 because you are actually trying to tell which cell is last (before the null) not which one is null
i = i + 1
Loop

'Sheets(j).PageSetup.PrintArea = ""
Application.PrintCommunication = False
With Sheets(j).PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&10" & "Appendix G Laboratory Data" & Chr(10) & "Groundwater" & Chr(10) & "Site Inspection Report, Site Name"
.RightHeader = ""
.LeftFooter = "&""Arial,Bold""&8" & "AECOM"
.CenterFooter = ""
.RightFooter = "&""Arial""&10" & "Appendix G-PFAS Groundwater" & Chr(10) & "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.85)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Zoom = 63
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True

End With


Next j

End Sub

 

[JoeMo]

.CenterHeader = "&""Arial,Bold""&10" & "Appendix G Laboratory Data" & Chr(10) & "Groundwater" & Chr(10) & "Site Inspection Report, Site Name"

Try changing the last bit after the & to:

"Site Inspection Report, " & MyInput

 

[sanityendshere]

@JoeMo Thank you so much that worked perfectly!

 

[JoeMo]

@JoeMo Thank you so much that worked perfectly!

You are welcome - thanks for the reply.