Syntax Error ?. Execution does not enter the Select Case

[u473]

I missed something here, because all Worksheets get printed, ignoring
my Select Case. Help appreciated
Sub SummaryPrint()
Dim A As String
Dim Sh As Worksheet
' Path to Projects Workbook
Workbooks.Open Filename:="C:\Report December 2007.xls"
Application.ActivePrinter = "LANIER LD160c PCL 6Floor4 on Ne01:"
For Each Sh In ActiveWorkbook.Sheets
' Each Worksheet tab is named like 52401, 61021, 61191 Etc...
Sh.Activate
A = Sh.Name
' Execution does not enter the Select Case, Why ?
Select Case A
Case "52401"
MsgBox "Printing", A
Case "61021"
MsgBox "Printing", A
Case Else
GoTo NoPrint
End Select
Range("B1:R9").Select
'-------------------------------------------
With ActiveSheet.PageSetup
..PaperSize = xlPaperLetter
..Orientation = xlLandscape
..Zoom = False
..FitToPagesWide = 1
End With
'-------------------------------------------
ActiveWindow.SelectedSheets.PrintOut Preview:=True
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,""LANIER LD160c PCL 6Floor4
on Ne01:"",,TRUE,,FALSE)"
NoPrint:
Next Sh
End Sub

 

[Don Guillett]

By does not enter you mean you get a "mismatch msg" then
MsgBox "Printing", A
should be
MsgBox "Printing" & A
You need not select the sheet to print it. You should avoid page setup in
the macro as it is slow. If you must, then ONLY change what is necessary.

You may prefer
Sub sheetsinarray()
myarray = Array("52401", "61021")
For Each sh In myarray
MsgBox Sheets(sh).Range("a2")
Next sh
End Sub

 

[Don Guillett]

You should keep all responses in the thread for the benefit of all. Didn't I
say

You need not select the sheet to print it

You cannot select a range in a sheet without selecting the sheet first. If
you MUST select use application.goto

For Each Sh In myarray'NO selection of sheet
With sheets(sh).PageSetup
..PaperSize = xlPaperLetter
..Orientation = xlLandscape
..Zoom = False
..FitToPagesWide = 1
End With
======
Your response sent to me
Thank you for your response,
I incorporated your suggestion but it is still not working yet.
Wasn't I supposed to Dim myarray ?
Sub SummaryPrint()
Dim A As String
Dim Sh As Worksheet
Dim myarray as String
' Path to Projects Workbook
Workbooks.Open Filename:="C:\Report December 2007.xls"
Application.ActivePrinter = "LANIER LD160c PCL 6Floor4 on Ne01:"
myarray = Array("52401", "61021")
For Each Sh In myarray
Sheets(sh).Range("B1:R9").Select
'-------------------------------------------
With ActiveSheet.PageSetup
..PaperSize = xlPaperLetter
..Orientation = xlLandscape
..Zoom = False
..FitToPagesWide = 1
End With
'-------------------------------------------
ActiveWindow.SelectedSheets.PrintOut Preview:=True
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,""LANIER LD160c PCL 6Floor4
on Ne01:"",,TRUE,,FALSE)"
NoPrint:
Next Sh
End Sub