Create "print array" button

**davidshluger** · 04-28-2010, 12:21 PM

Hello all, could use some help here...

I have an Excel workbook with 10 tabs or so. One tab is a "Print" worksheet. On this worksheet, I have created two buttons in VB -one is "Print Results Only", and the other is "Print Questionaire and Results." The latter is giving me trouble...

Private Sub CommandButton2_Click()
    Sheets(Array("X-Ray Assessment", "ERM Framework", "Operational Risks", "Strategic Risks", "People Risks", "Market Risks", "Financial Risks", "Business Unit - Questions", "Business Unit - Score")).Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Can anyone point out the problem?! I am getting an error that says "Runtime error (9) ... Subscript out of range." All of the names are an exact match, so i can't figure out the problem.

**shg** · 04-28-2010, 12:37 PM

Welcome to the forum.

Please take a few miinutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

**davidshluger** · 04-28-2010, 12:48 PM

I appologize shg, thanks for your help. I did not mean to be an inconsiderate forum user and would genuinely appreciate some assistance. I have been searching extensively for a fix on this and saw a couple that were close (ironically fixed by you!) but not close enough for my unsophisticated abilities. Any ideas?

**JBeaucaire** · 04-28-2010, 01:33 PM

That error would cause me to suspect the sheet names. I know you said they all exist, but spelling counts, and the most common stumble for me is a hidden space somewhere in one of the sheetnames.

Try removing all but one sheet from your array, and then test. If it works, add another sheet to the array and test...etc. At some point you'll add a sheet and it won't work and now you'll know where you have to look.

**davidshluger** · 04-28-2010, 02:57 PM

Wow that did it! I feel silly but there was indeed a space after a bunch of the tab titles. Thanks! Ok let's see if I can stump you with this...

I made a spin button and linked it to cell c24. The idea is use cell c24 as an input value for the number of copies to print from the print button. Follow? This is what I tried, without it working...

Private Sub CommandButton3_Click()
    Sheets("Business Unit - Questions").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=(c24), Collate:=True
End Sub

Any idea how to make Copies = [value in c24] ???

**JBeaucaire** · 04-28-2010, 04:15 PM

Like so:

Private Sub CommandButton3_Click()
    ActiveWindow.SelectedSheets.PrintOut _
        Copies:=Sheets("Business Unit - Questions").Range("C24"), Collate:=True
End Sub

**davidshluger** · 04-28-2010, 04:24 PM

I didn't explain that right. Cell C24 is a number on the active sheet, but the sheet to be printed is a seperate worksheet. I am very proud to say that with your help I fixed it myself using the following:

Private Sub CommandButton3_Click()
    Sheets("Business Unit - Questions").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=Range("c24"), Collate:=True
End Sub

Worked like a charm. Thanks for all your help!

**JBeaucaire** · 04-28-2010, 05:31 PM

As a matter of habit, you want to make sure you edit out any Selecting going on your macros. You see that because you're starting with recorded code, we all do that, but you need to edit it back out again.

Private Sub CommandButton3_Click()
    Sheets("Business Unit - Questions").PrintOut _
        Copies:=ActiveSheet.Range("C24"), Collate:=True
End Sub

....or maybe:

Private Sub CommandButton3_Click()
    Sheets("Business Unit - Questions").PrintOut _
        Copies:=Me.Range("C24"), Collate:=True
End Sub

...or list the sheet directly:

Private Sub CommandButton3_Click()
    Sheets("Business Unit - Questions").PrintOut _
        Copies:=Sheets)("Sheet1").Range("C24"), Collate:=True
End Sub

==========
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.