Excel - specifying a variable to use across all headers/footers

[smooveb]

Hi,

the problem is simple. We need to be able to specify a version number, and have it included in all of our footers. It seems header/footer info needs to be specified separately for each worksheet in a book?

I'm open to suggestions on any different way to go about this, whether it involves simply functionality or vba. Thanks in advance experts!

Barrett

 

[smooveb]

One workaround I thought might work (if no other good ideas are forthcoming) would be to provide a userform at the print event (how can one do this?) for the document. The userform would take the appropriate input (say, version number and author or something) and then apply these values to all the headers before continuing with the print. not sure about the code surrounding a print event though...

Barrett

 

[Simmy]

Hi

Using VBA, you could use the following code, changing the text "Hello" to whatever you wish

Sub Macro1()
For Each Sh In Worksheets
Sh.PageSetup.CenterFooter = "Hello"
Next Sh
End Sub

Regards

Simmy

 

[smooveb]

Thanks Simmy!

How can I incorporate this code, seamlessly, into any print event for the document? Using the above in a practical example, I'd pick up the string (above it would equal 'hello') from a cell on the first worksheet - would probably end up being the version number.

From that point forward, don't want the user to have to do anything but press print, and have that string be applied automatically using the code above. Is this possible?
Thanks!
Barrett

 

[smooveb]

i'm wondering if i should bump this since it's been two days

Thanks!
Barrett

 

[rmikesmith]

Barret,

Post your email address and I'll send you a demo workbook that pulls all this together.


Regards,
Mike

 

[Simmy]

Sorry I have been away for a few days

The Routine below should do all you require providing Your version number is in Cell A1 of Sheet 1

Sub Macro1()
Sheets("Sheet1&quot.Activate
Range("A1&quot.Select
VersionNo = ActiveCell.Formula
For Each Sh In Worksheets
Sh.PageSetup.CenterFooter = "Version " & VersionNo
Next Sh
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
End Sub

Regards

Simmy