Macro works in 2010 but not 2007

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote