Excel macros & cell reference

[swahid]

Hi,

I have a number of excel files containing 6-10 worksheets each Every month I need to change the header and footer with new dates before I insert some data. I need a macro which will take the contents of three cells and copy this into the headers and footers.
Note: the dates change every month.
I have tried using a macro recorded on a separate file but it only allows me one set of dates which I cannot change.

Any help much appreciated.

Thanks

 

[Asala42]

G'day,

You can probably use something quick like this:

Code

Sub MakeHeader()
Sheets("Sheet1").PageSetup.CenterHeader = Range("A1").Value
End Sub

That should take the value in Sheet1!A1 and toss that into your center header.

Hope that helps,
Adam

 

[swahid]

Thanks for the reply. I am not sure how to use this code, is it part of a macro? Please let me know how I can get this to work for all worksheets and if possible how to do this step-by-step. Again many thanks for your help.

 

[Asala42]

Sure thing,

You'll probably want to use this or a similar macro per file. I'll take an example of a new file with 3 sheets - the code below can be used to take the cell A1 on each sheet and make it the sheet's center header.

If you hit Alt+F11 you'll be in your VBA editor. Go to Insert/Module and paste the following:

Sub MakeHeader() 
Sheets("Sheet1").PageSetup.CenterHeader = Sheets("Sheet1").Range("A1").Value

Sheets("Sheet2").PageSetup.CenterHeader = Sheets("Sheet2").Range("A1").Value

Sheets("Sheet3").PageSetup.CenterHeader = Sheets("Sheet3").Range("A1").Value

End Sub

You'll need to tweak the code a little to match sheet names and so forth. After which, you can then access the macro by going to Tools/macros.

If you have time to play around with vba the macro recorder can be extremely useful by the way.

Hope that helps somewhat,
Adam

 

[swahid]

Adam,

Many thanks for all your help. I will give this a go.

Thank you