custom Header-Automated

J

juan

Hello,
I have a custome header Example,
(REGION) - REPORT NAME PERIOD X, 2004

So before I print, I have to fill in what region Example
NA, EUROPE Or ASIA, plus what period I'm doing.
Is it possible to use a macro to fill the region and
period instead of doing manually to the menu toolbar,
Format/header/footers?

Not sure if I'm clear.
Please advise any info.

Thank you,

Juan
 
F

Frank Kabel

Hi Juan
if the region is in cell A1 and the period in cell B1 of your sheet try
the following macro. Put it in your workbook module (not in an standard
module):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Dim head_str
For Each wkSht In Me.Worksheets
head_str = wkSht.range("A1").value & _
" - REPORT NAME PERIOD " & _
wkSht.range("B1").value & ", 2004"
With wkSht.PageSetup
.CenterHeader = head_str
End With
end with
Next wkSht
End Sub
 
J

Juan

Hello Frank,
thanks, but I think I wasnt' clear. The custom header I
go to Page setup and there's a tab to put custom
headers/footers. So I guess before user prints, they
would have to manually go to Page Setup and go to the
HEADER/FOOTER tab and just type in the region and period.
Thanks for the info. But if you understand what I'm
trying to do please provide info. Thanks alot.

juan
 
F

Frank Kabel

Hi
yes this is the manual way. But you asked for a macro solution :)
The macro from my previous post will allow to just put these values
(region, period) in a cell on your worksheet (or use existing cells)
and automatically fill themn into the header/footer directly before you
print this site.

So have you tried the macro?
 
J

Juan

Hello Frank,
thanks. No haven't tried macro will do. I do appreciate
all the help you have provided me.

Thanks alot.

juan
 
Top