Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Macro works in 2010 but not 2007

    I have the following macro that works in 2010 when I click on a button on the spreadsheet that launches the macro.
    However, when I gave the workbook to my user that runs 2007, it doesn't work.

    Sub PrintProposal()
    '
    ' PrintProposal Macro
    '

    '
    Sheets(Array("Proposal", "Spreadsheet")).Select (<-- the error is on this line)
    Sheets("Proposal").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
    Sheets("CustomerData").Select
    End Sub


    HELP! Thanks in advance.

    May have found a clue. 2010 no longer works now either...I think because I HIDE the sheets that are being printed, save the workbook, and hope it works.

    How can I have this work and also have the sheets hidden -- so that the user only has 1 sheet and the print option buttons.
    Last edited by kweaver; 2012-04-26 at 18:40.

  2. #2
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Hi

    Macro runs fine for me in both XL2007 and XL2010.

    However, I cannot see why you would want to use
    Sheets(Array("Proposal", "Spreadsheet")).Select '(<-- the error is on this line

    (you were missing the rem ' mark on the above line)

    Just use
    Sheets("Proposal").Activate
    Regards
    Roger Govier
    Microsoft Excel MVP

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    my "(<-- ... " was NOT in the macro... it was just for this post to show where the error was.

    But, it seems that the error happens because I hid the sheets. Is there a way to make the macro work when the sheets are hidden? I'm trying to get the macro to select BOTH of those sheets and print both of them, but want both of them hidden.

  4. #4
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Hi

    Then try
    Code:
    Sub PrintProposal()
    '
    ' PrintProposal Macro
    
    
    Sheets("Proposal").Visible = xlSheetVisible
    Sheets("Spreadsheet").Visible = xlSheetVisible
    Sheets(Array("Proposal", "Spreadsheet")).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
    Sheets("Proposal").Visible = xlSheetHidden
    Sheets("Spreadsheet").Visible = xlSheetHidden
    Sheets("CustomerData").Select
    
    
    End Sub
    Regards
    Roger Govier
    Microsoft Excel MVP

  5. The Following User Says Thank You to Roger Govier For This Useful Post:

    kweaver (2012-04-26)

Posting Permissions

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