Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax Error ?. Execution does not enter the Select Case
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax Error ?. Execution does not enter the Select Case
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software "u473" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie: Problem with 'Select Case' testing syntax | Excel Programming | |||
Select Case syntax | Excel Programming | |||
Select Case for Active Worksheet syntax. | Excel Programming | |||
Syntax for Select Case | Excel Programming | |||
Select Case syntax | Excel Programming |