G
ghamilton
I use a separate workbook for my commonly used macros. I want to
store some default header labels also, but I cannot get it to "paste"
the value on cell "N10" in workbook "B" over to the left header on
workbook "A". Right now I use this macro to prompt me with dialog
boxes. The first and third input boxes are going to be the same for
all workbooks done in a month. Another option would be to populate
the dialog box with a value located in a certain cell on my macro
workbook.
Sub header()
Dim pos As Integer
With ActiveSheet.PageSetup
If .LeftHeader <> "" Then
pos = InStr(1, .LeftHeader, Chr(10))
If pos > 0 Then
Period = Left(.LeftHeader, pos - 1)
divname = Right(.LeftHeader, Len(.LeftHeader) - pos)
End If
End If
If .CenterHeader <> "" Then
pos = InStr(1, .CenterHeader, Chr(10))
If pos > 0 Then
title1 = Left(.CenterHeader, pos - 1)
title2 = Right(.CenterHeader, Len(.CenterHeader) - pos)
End If
End If
End With
Period = InputBox("What Period?", , Period)
divname = InputBox("What Division?", , divname)
title1 = InputBox("CENTER TITLE ROW 1", , title1)
title2 = InputBox("CENTER TITLE ROW 2", , title2)
With ActiveSheet.PageSetup
.LeftHeader = Period & Chr(10) & divname
.CenterHeader = title1 & Chr(10) & title2
.RightHeader = Date
.RightFooter = Page
End With
End Sub
store some default header labels also, but I cannot get it to "paste"
the value on cell "N10" in workbook "B" over to the left header on
workbook "A". Right now I use this macro to prompt me with dialog
boxes. The first and third input boxes are going to be the same for
all workbooks done in a month. Another option would be to populate
the dialog box with a value located in a certain cell on my macro
workbook.
Sub header()
Dim pos As Integer
With ActiveSheet.PageSetup
If .LeftHeader <> "" Then
pos = InStr(1, .LeftHeader, Chr(10))
If pos > 0 Then
Period = Left(.LeftHeader, pos - 1)
divname = Right(.LeftHeader, Len(.LeftHeader) - pos)
End If
End If
If .CenterHeader <> "" Then
pos = InStr(1, .CenterHeader, Chr(10))
If pos > 0 Then
title1 = Left(.CenterHeader, pos - 1)
title2 = Right(.CenterHeader, Len(.CenterHeader) - pos)
End If
End If
End With
Period = InputBox("What Period?", , Period)
divname = InputBox("What Division?", , divname)
title1 = InputBox("CENTER TITLE ROW 1", , title1)
title2 = InputBox("CENTER TITLE ROW 2", , title2)
With ActiveSheet.PageSetup
.LeftHeader = Period & Chr(10) & divname
.CenterHeader = title1 & Chr(10) & title2
.RightHeader = Date
.RightFooter = Page
End With
End Sub