Selecting all sheets to print without knowing tab names

imbn2wild

New Member
Joined
Jan 29, 2004
Messages
22
I am trying to print all sheets within a workbook without knowing the name of the tabs. I do not want the user to have to do this.
I am not sure why this doesn't work. Any ideas?

Code:
Public Sub ListPrintSheets()
   ' An individual entry.
   Dim ThisEntry As Variant
   Dim PrintArray As Variant
   Dim Output As Variant
   
   For Each ThisEntry In Application.Sheets
      If Output = "" Then
      Output = ThisEntry.Index
      
      Else
         Output = Output & ", " & ThisEntry.Index
      End If
      Next
PrintArray = Array(Output)
   
    Sheets(PrintArray).Select
 '  Sheets(Array(Output)).Select

End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi imbn2wild:

How about using ...
Code:
ActiveWorkbook.PrintOut Copies:=1
Would that help?
 

imbn2wild

New Member
Joined
Jan 29, 2004
Messages
22
Hi Yogi,

That works for just printing, but,

My thought here is to leave the workbook with all tabs selected, then save it, then if the user wants to visually hit print preview, prior to printing it, the selections are all made....
 

imbn2wild

New Member
Joined
Jan 29, 2004
Messages
22
a new idea, based upon yogi's thoughtful suggestion:

Code:
Sub newidea()
ActiveWorkbook.Sheets.Select
End Sub

THANKS :LOL:
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
imbn2wild said:
Hi Yogi,

That works for just printing, but,

My thought here is to leave the workbook with all tabs selected, then save it, then if the user wants to visually hit print preview, prior to printing it, the selections are all made....
I don't think it is a good idea to have all the tabs in the workbook selected all the time -- if you do want to offer the user the opportunity to PreView before Printing, I would suggest modifying the code to ...
Code:
ActiveWorkbook.PrintOut Copies:=1, Preview:=True
now with this code, the macro will take the user to the PrintPreview from where the user may choose among other options to Print, or Close, or ...
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,140
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top