VBA: Printing all but 2 sheets in a work book.

[concretemonkey]

Ok, firstly, i would like to say hello.

Secondly i would like to ask for your !!

I would like to place a button on the first sheet of my work book that will allow my users to print all of the pages in the workbook.

I dont want them to be able to print the first page, and i dont want them to be able to print hidden pages. Also, every other sheet is a chart. I have been given help, but i didn't mention that some of the sheets were charts, so it only prints out the worksheets.

I would also like the printouts to be in order.

here is the code that I was given:

Sub PrintMost() 
    Dim data() As String, w As Worksheet, i As Integer 
    i = 0 
    For Each w In ActiveWorkbook.Worksheets 
        If w.Index <> 1 And w.Visible Then 
            i = i + 1 
            ReDim Preserve data(1 To i) 
            data(i) = w.Name 
        End If 
    Next w 
    If i > 0 Then Worksheets(data).PrintOut 
End Sub

from this i came up with the following code that will print all the sheets i want, but not in order:

Private Sub CommandButton1_Click() 
Dim data() As String, data2() As String, w As Worksheet, i As Integer, c As Chart, i2 As Integer 
i = 0 
i2 = 0 
For Each w In ActiveWorkbook.Worksheets 
If w.Index <> 1 And w.Visible Then 
i = i + 1 
ReDim Preserve data(1 To i) 
data(i) = w.Name 
End If 

Next w 

If i > 0 Then Worksheets(data).PrintOut 

For Each c In ActiveWorkbook.Charts 
If c.Index <> 1 And c.Visible Then 
i2 = i2 + 1 
ReDim Preserve data2(1 To i2) 
data2(i2) = c.Name 
End If 
Next c 

If i > 0 Then Charts(data2).PrintOut 

End Sub

I would really appreciate the help, as it is starting to aggrovate me.

I have tried everything that i know. Please help.

Thank you in advance,

Gareth.

 

[Andrew Poulsom]

You can use the Sheets collection, which contains both Worksheets and Charts. Example:

Sub Test()
    Dim Sh
    For Each Sh In Sheets
        MsgBox Sh.Name
    Next Sh
End Sub

 

[emady]

You can try this:

Sub CommandButton1_Click()
Dim Sh
Dim i As Integer
i = 0
For Each Sh In Sheets
If Sh.Index <> 1 And Sh.Visible Then
i = i + 1
If i > 0 Then Sh.PrintOut
End If
Next Sh
End Sub

Elia

 

[concretemonkey]

You can try this:

Sub CommandButton1_Click()
Dim Sh
Dim i As Integer
i = 0
For Each Sh In Sheets
If Sh.Index <> 1 And Sh.Visible Then
i = i + 1
If i > 0 Then Sh.PrintOut
End If
Next Sh
End Sub

Elia

OMG! Thank you ever so much.

After looking at the first code i was given, i set sh as Sheets, so the .inex wouldnt work.

That code works a treat. Thank you ever so much. I have been trying to sus this out since 8am this morning. My head hurts and i am due a coffee. thank you. thank you. thank you.

- heres to you guys!!

time!!