Re: custom Header-Automated

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 04/16/04 

Date: Fri, 16 Apr 2004 07:16:41 +0200

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 work*** (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 ***
>> 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 Work***
>> 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
>>
>> .
Quantcast