Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Excel macros & cell reference

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
    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


  5. #5
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Adam,

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

    Thank you

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •