Syntax Error ?. Execution does not enter the Select Case

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

u473 - 11 Dec 2007 13:45 GMT

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

Reply to this Message

Don Guillett - 11 Dec 2007 14:06 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I missed something here, because all Worksheets get printed, ignoring
> my Select Case. Help appreciated
[quoted text clipped - 32 lines]
> Next Sh
> End Sub

Reply to this Message

Don Guillett - 11 Dec 2007 16:56 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>
> By does not enter you mean you get a "mismatch msg" then
[quoted text clipped - 47 lines]
>> Next Sh
>> End Sub

Reply to this Message