willyboy58
Technical User
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"
.Activate
For Each Sheet In Workbooks("Prop1" & ReportMonth & ".xls"
.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"
.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
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"
For Each Sheet In Workbooks("Prop1" & ReportMonth & ".xls"
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"
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