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

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



--
Regards
Frank Kabel
Frankfurt, Germany


juan wrote:
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

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
-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany


juan wrote:
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

.

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?


--
Regards
Frank Kabel
Frankfurt, Germany


Juan wrote:
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
-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany


juan wrote:
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

.

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

Thanks alot.

juan
-----Original Message-----
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?


--
Regards
Frank Kabel
Frankfurt, Germany


Juan wrote:
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
-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany


juan wrote:
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

.

.