Excel Macro/VB Error Message

[mtdew]

I had a macro I developed in Excel that I was trying to incorporate into visual basic to print a specific range from each sheet except for sheets with specific names. But it doesn't seem to move off of the first sheet, named Menu, and it wants to print what is on the menu sheet. Thus far I have:

Private Sub CommandButton3_Click()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "Template" And sh.Name <> "names" And sh.Name <> "Menu" And sh.Name <> "Reports" And sh.Name <> "Master" Then

Range("X1:AG43").Select
Selection.PrintOut Copies:=1

End If
Next
On Error Resume Next
End Sub

Any suggestions?

 

[Gavona]

You need to re-post in the VBA (=macro) forum - forum707

When posting programming code use TGML: precede the code with the

tag and end it with [ /code] tag. This will aid in formatting your programming code correctly.

Before your IF add the line:
MsgBox (sh.Name) or add a watch on sh.name

Then step through your code to see what is happening.
Are the sheets in the workbook containing the code (ThisWorkbook)?



Gavin

 

[mtdew]

I did as you suggested: reposted tried the MsgBox. The MsgBox showed me it was going through each of the sheets. Apparently what I'm doing wrong is going through each of the sheets but only printing the range from the first sheet. Do you know how to express it?
Something like:

sh.name.Range("X1:AG43").Select?

 

[Gavona]

thread707-1562238

Gavin