Looping thru Select Case/Out of Range problem 1

[willyboy58]

I am wanting to do some previewing and printing with Macros assigned to six command buttons on a worksheet. Three for previewing and three for printing. I want to preview and/or print “Invoices and Detail” (which is everything), “Invoices Only” and “Detail Only”. I have the following worksheets (in order from left to right) in an activated workbook:

InvoicePage1
InvoicePage2
InvoicePage3
Clntrates
NOVA-PT
SATL-PT
WYND-PT
MAIN-PT
PROP1ALL

I have three pieces of code: PrintRequirementsPortrait, PrintRequirementsLandscape and PrintWithSelectCase. The first two procedures are the properties desired (font, paper size, etc.) and the third procedure calls one of the two based upon the worksheet being processed. I am wanting to loop through and print all of the sheets. The code below is for printing, I’ll write another for the preview.

Here is my code to call the proper procedure:

Sub PrintWithSelectCase()
Dim Sheet As Worksheet
Workbooks("Prop1" & ReportMonth & ".xls&quot.Activate
For Each Sheet In Workbooks("Prop1" & ReportMonth & ".xls&quot.Worksheets

Select Case Sheet
Case Is = "Invoice1" Or "Invoice2" Or "Invoice3"
PrintRequirementsPortrait
Case Is = "ClntRate"
PrintRequirementsPortrait
Case Is = "SATL-PT" Or "WYND-PT" Or "MAIN-PT"
PrintRequirementsLandscape
Case Is = "PROP1ALL"
PrintRequirementsPortrait
End Select

Next Sheet

End Sub

I am getting the message “Subscript out of range” on the line:

Workbooks("Prop1" & ReportMonth & ".xls&quot.Activate

This workbook is open when I run the procedure. The variable “ReportMonth” is a string value from an input box. The file is saved and then reopened. I am wondering if my procedure is not reading the file name.

Plus is there a better way to accomplish this task of looping through the worksheets?

TIA

Bill

 

[SkipVought]

Bill,

You need sheet NAME in the select

        Select Case Sheet.Name

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

I made the change you suggested (Sheet.Name) plus a few others. Below is the new code:

Sub PreviewWithSelectCase()
Dim Sheet As Worksheet
Workbooks("Prop1" & ReportMonth & ".xls&quot.Activate
With ActiveWorkbook

For Each Sheet In Workbooks("Prop1" & ReportMonth & ".xls&quot.Worksheets

Select Case Sheet.Name
Case Is = "InvoicePage1" Or "InvoicePage2" Or "InvoicePage3"
PrintRequirementsPortrait
Case Is = "ClntRate"
PrintRequirementsPortrait
Case Is = "SATL-PT" Or "WYND-PT" Or "MAIN-PT"
PrintRequirementsLandscape
Case Is = "PROP1ALL"
PrintRequirementsPortrait
End Select

Next Sheet
End With
End Sub

Please note that the workbook "Prop1" & ReportMonth & ".xls" is definitely open when I run the above code, but I still get the message “Subscript out of range”. But when I place the PreviewWithSelectCase procedure in the code below, I get past the Subscript message, but then I get a Type Mismatch message at the first “Case Is” line. This tells me (I think) that the code above is not detecting the "Prop1" & ReportMonth & ".xls" workbook.

I’m telling you about the code below to maybe help describe the problem. I do not want the PreviewWithSelectCase code in the procedure below. I want to assign it to a button to run separately.

Sub OpenReportMonthWorkbook2()
Workbooks.Open Filename:="Prop1" & ReportMonth & ".xls", _
UpdateLinks:=3

PreviewWithSelectCase
End Sub

Any thoughts?

Bill

 

[SkipVought]

The Select Case statement does NOTHING! You are calling the same procedure AND the Sheet.Name is NOT passed to the procedure. The PrintRequirementsPortrait needs EITHER the Sheet object or the sheet Name to be able to format the print setup and PrintOut the Sheet. The Sheet does not necessarily need to be activated to do this, but it needs to be referenced.

Sub PreviewWithSelectCase()
    Dim Sheet As Worksheet
    With Workbooks("Prop1" & ReportMonth & ".xls")
        For Each Sheet In .Worksheets
            Select Case Sheet.Name
                Case "InvoicePage1", "InvoicePage2", "InvoicePage3"
                PrintRequirementsPortrait
                Case "ClntRate"
                PrintRequirementsPortrait
                Case "SATL-PT", "WYND-PT", "MAIN-PT"
                PrintRequirementsLandscape
                Case "PROP1ALL"
                PrintRequirementsPortrait
            End Select
        Next Sheet
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

Thanks for the help. I got it to loop now, but am having problems with page viewing. I made a few changes to the original code.

All of the "-PT" sheets should be getting the Landscape when viewed, but they are not. Of the "-PT" sheets, which ever sheet is selected in the window when the workbook is visible, is the sheet that gets Landscape during the looping and previewing. The other "-PT" sheets are getting the Portrait.

Sub aPreviewWithSelectCase2()
Dim sheet As Worksheet
Dim wbToOpen As String

MonthAndYear 'procedure with input box for selecting _
month to view or print. Variable name is ReportMonth

wbToOpen = "Prop1" & ReportMonth & ".xls"
Workbooks.Open Filename:="C:\MSOffice\Excel\Work Stuff\" & wbToOpen
Sheets(&quot;SATL-PT&quot.Select '<<< this line for testing only.
'above line will be removed

With ActiveWorkbook
For Each sheet In .Worksheets

Select Case sheet.Name
Case &quot;InvoicePage1&quot;, &quot;InvoicePage2&quot;, &quot;InvoicePage3&quot;
sheet.PrintPreview
Case &quot;CLNTrate&quot;
sheet.PrintPreview
Case &quot;PROP1ALL&quot;
sheet.PrintPreview
'all of the above get the Portrait, which is OK.

'All &quot;-PT&quot; sheets s/b getting Landscape & Zoom, only one is.
Case &quot;SATL-PT&quot;, &quot;WYND-PT&quot;, &quot;MAIN-PT&quot;, &quot;NOVA-PT&quot;
With ActiveSheet
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = 70
End With
sheet.PrintPreview

End Select
Next sheet
End With
End Sub

As always, TIA.

Bill

 

[SkipVought]

sheet.PrintPreview is not a proper statement. I assume that PrintRerview is a procedure. Within that procedure, Sheet needs to be the referenced object with respect to the formatting.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

I tried your code and it loops thru (I can see the screen/worksheets wiggling just a bit), but it does not give me the opportunity to see the sheets in preview mode. In your last post you said “sheet.PrintPreview is not a proper statement. I assume that PrintRerview is a procedure. Within that procedure, Sheet needs to be the referenced object with respect to the formatting.” The Portrait and Landscape proc’s have been renamed to PreviewPagesPortrait and PreviewPagesLandscape, but how do I do what you suggest?

I’ve made a few changes trying to get this to work. My new code is:

Sub PreviewPagesPortrait()
With ActiveSheet.PageSetup
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
End Sub

Sub PreviewPagesLandscape()
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
End Sub

‘the following selects each sheet in a workbook and previews it with either Portrait or Landscape. Unfortunately, it gives me Portrait for all the sheets except for NOVA-PT. But NOVA-PT, SATL-PT, MAIN-PT and WYND-PT should all be Landscape. It loops OK, the Preview is the problem.

Sub PreviewAllPages()
Dim sh As Worksheet
ReportMonth = InputBox(&quot;Enter Month and Year to view&quot
Workbooks.Open _
Filename:=&quot;Prop1&quot; & ReportMonth & &quot;.xls&quot;

With ActiveWorkbook
For Each sh In .Worksheets

Select Case sh.Name
Case Is = &quot;InvoicePage1&quot; Or &quot;InvoicePage2&quot; Or &quot;InvoicePage3&quot;
PreviewPagesPortrait
Case Is = &quot;ClntRate&quot;
PreviewPagesPortrait
Case Is = &quot;PROP1ALL&quot;
PreviewPagesPortrait

Case Is = &quot;NOVA-PT&quot;
PreviewPagesLandscape
Case Is = &quot;MAIN-PT&quot;
PreviewPagesLandscape
Case Is = &quot;WYND-PT&quot;
PreviewPagesLandscape
Case Is = &quot;SATL-PT&quot;
PreviewPagesLandscape

End Select
sh.PrintPreview

Next sh
End With

End Sub

TIA

Bill

 

[SkipVought]

A program loop is not designed to view a sheet or a preview. I guess I have missunderstood your intentions.

Tell me what you want this procedure to do.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

I have the following two worksheets:
Clntrates ‘raw data
PROP1ALL ‘raw data

I have the following worksheets that have the finished processed data:

InvoicePage1
InvoicePage2
InvoicePage3
NOVA-PT
SATL-PT
WYND-PT
MAIN-PT

Here is basically what I need to do.

After processing some raw data (from Prop1All and Clntrates worksheets above), I need to be able to click a control (Button on a worksheet with an assigned macro) and preview all of the worksheets that have the final data on them (the other worksheets above). Some of the sheets will be previewed in Portrait (Invoices for example) and some in Landscape (data breakdown by shops for example). I want to preview before printing so I can make sure of the correct month, year and any other issues before I print them. Plus lookup if needed.

After viewing, I want to be able to print. I will have three buttons for the printing: 1) Invoices only (in Portrait) 2) Data sheets (Landscape) and 3) Both Invoices and data. I need the Landscape b/c the data is for about 20-23 days of the month and it is easier to read across. Plus, it has to be submitted to the county in landscape report format. (I’m thinking that if I can get the previewing working, then I should be able to get the printing aspect also.)

I may want to view and/or print the other two sheets “CLNTRATE” and “Prop1All”, but they are not required for viewing or printing.

I don’t believe that either the code that you gave me or what I did is really that far off from what I need, I just can’t get it to do what I need. As always, thanks for the help.

Congratulations on tipster of the week.

Bill

 

[SkipVought]

Then you don't want just a loop. You want to be able to select the NEXT sheet to print preview.

The logic would be...

1. assign the current sheet's index to IDX
2. incriment IDX
3. if IDX is greater than the sheet count then IDX = 1
4. if Sheet(IDX) is a sheet to preview then preview otherwise step 2

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

Thanks for the help. I got it to loop the way I want it to, though I’m not sure if I understood what you suggested.

'the following allows the user to preview the sheets b/f printing. the sub loops thru the sheets and gives Portrait to the Invoices sheets and Landscape to the detail sheets (PT's).

Sub aPreviewAllPagesxxx()
Dim sh As Worksheet
Dim sheetscount As Integer
Dim sheetnumber As Integer

sheetnumber = 1
sheetscount = Worksheets.Count

ReportMonth = InputBox(&quot;Enter Month and Year to view&quot
Workbooks.Open Filename:=&quot;Prop1&quot; & ReportMonth & &quot;.xls&quot;

With ActiveWorkbook
'starts at first sheet on the left
Worksheets(&quot;InvoicePage1&quot.Activate
Do
For Each sh In .Worksheets
If InStr(1, sh.Name, &quot;Invoice&quot Then
With ActiveSheet.PageSetup
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
sh.PrintPreview
Else
If InStr(5, sh.Name, &quot;-PT&quot Then
With sh.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaper11x17
.FitToPagesTall = False
.FitToPagesWide = 1.5
.Zoom = 65
End With
sh.PrintPreview
End If
End If
Next sh
sheetnumber = sheetnumber + 1
Loop Until sheetnumber <= sheetscount
End With
End Sub

I found an article on MS knowledge base # (111943) that I believe is the same issue, but I couldn't figure out how to do theirs.

Bill