Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically Update Footer (2000)

    Is there a way in Excel to set a part of the footer to be the same in all Sheets? i.e. I have a spreadsheet workbook that contains 15 sheets (tabs). The Left side footer would have the section (sheet) title ~ In-Plan Services, Quality Management, etc. However the Right Side footer has the Revision date of the document. Is there a way that I can set it so the Revision date will appear in the footer of all sheets then if this changes I only need to change it in one spot. Otherwise I have to go into all 15 sheets and change it 15 times. It seems like there would be an easier way. Any help you can provide would be appreciated. THANKS
    Deb

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Update Footer (2000)

    Something along the following lines should help. This makes the right footer in the activesheet the same as whatever is in Sheet 1, Cell A1. Look in the help file for more stuff on pagesetup. You can put this in a macro and call it when you need it, or put it into the Thisworkbook_BeforePrint Event.

    With ActiveSheet.PageSetup
    .RightFooter = Sheets(1).Range("a1").Value
    End With

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Update Footer (2000)

    What do you consider the revision date? The workbook file last modified date? A date in a cell someplace (if so, what cell on what sheet)? A date that is set when any sheet in the workbook is changed? Something else?
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Automatically Update Footer (2000)

    Hi,

    While a macro is probably the easiest way to go to solve your specific problem, you don't seem to be aware that you can select all the sheets (Select the first sheet, then hold Shift down and select the last) and then whatever you do in Excel applies to all the selected sheets. This makes your initial mod to the Revision date very easy.

    Regards,
    Peter Moran

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Update Footer (2000)

    Peter,
    I did know about selecting all sheets but didn't think it would work for page setup items. It seems to work and I think it will work for what my customer wants. Thanks,
    Deb

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Automatically Update Footer (2000)

    In my experience selecting multiple sheets doesn't work all the time.
    For me it depended on how many sheets were selected, and in some cases caused problems - like crashing Excel.
    I have avoided this method ever since.
    I mostly use something like the Workbook BeforePrint in conjunction with a named range
    e.g ActiveSheet.PageSetup.RightFooter = [rightFooter]

    zeddy

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Update Footer (2000)

    Mike,
    Can you elaborate a little. I'm not that familiar with vba and am not sure where to put it or where to find Thisworkbook_before printevent. Thanks,
    Deb

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically Update Footer (2000)

    Sorry about the delay in getting back...been to the beach for a few days. To get to the Visual Basic Editor, go to Tools/Macro/Visual Basic Editor. Once inside, you should see the project explorer panel on the left and a somewhat blank screen on the right. There are two dropdowns at the top of the right screen, one will probably say "General", and one will probably say "Declarations". Double click on the "ThisWorkbook" object in the left screen (in a new book, you will have sheet1, sheet2, sheet3, and ThisWorkbook.

    In the first dropdown in the right panel, change it to "Workbook", and change the second dropdown to "BeforePrint". You should get a screen like the one below. I have put sample left and right footers in the pic below, change according to your liking.

Posting Permissions

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