Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Vba Code For Printing Variable Print Ranges

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello,
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2".

If I replace WhatToPrint with "Report1,Report2" the print macro works. Can anyone help me to understand why it doesn't work when I leave WhatToPrint in?

Really appreciate your help with this!

Jon

VB:

Sub Macro2() 
     '
     ' Macro2 Macro
     ' Macro recorded 05/05/2009 by JW8836
     '
     
     '
     
    Dim WhatToPrint As String 
     
     
     
    WhatToPrint = Sheets("Reports").Cells(3, 6).Value 
     'sets the variable to equal the contents of cell D3 which contains the formula
     'summarising the print ranges I want to print
     
     
     
    Sheets("Reports").Cells(3, 6).Select 
    ActiveCell.FormulaR1C1 = WhatToPrint 
     ' pastes the variable in cell F3 - just to check that it looks like I want it to
     
    Sheets("Reports").PageSetup.PrintArea = WhatToPrint 
     'uses the variable to set print area - this is where it fails!
     'if you replace the variable with the contents of cell F3 the macro will work
     
    ActiveWindow.SelectedSheets.PrintPreview 
     
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Print Preview Display for Specific Worksheets in Excel
- This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
Print Specific Pages in Excel
- This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print 2 pag
Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
Print Preview Screen Display for The Current or Selected Worksheets in Excel
- This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in

Similar Topics







Hi all,

I had the following macro and when generated, it shows nicely the print preview. But after exiting print preview, the page count shows otherwise and using page break preview mode, it shows that the print area sets all the way to the max row of 65 over thousands. please kindly help to see where I could be wrong.

Quote:

LastPrintRow = destSh.Range("A65535").End(xlUp).Row
Last = Lastprintcol(destSh)
Set WhatToPrint = destSh.Range("A1").Resize(LastPrintRow, Last)
With destSh.PageSetup
.PrintTitleRows = "$1:$21"
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
.PrintErrors = xlPrintErrorsDisplayed
'.Zoom = 52
End With
pages = ExecuteExcel4Macro("Get.Document(50)")
destSh.Range("G12").Value = pages
WhatToPrint.PrintPreview

Thanks,
CL





Hi there,

I am struggling with two print issues in an excel workbook. The workbook has sheets A, B, C and D.

Question 1
I always print to a PDF and have used the following macro which works well.

Sub PrintArrayOfWorksheets()

Dim vaWorksheets As Variant

vaWorksheets = Array("A", "B")

ThisWorkbook.Worksheets(vaWorksheets).PrintOut

Sheets("A").Select

The issue is that I now have additional sheets and the sheets to print can change but can be determined by the text in cell A1 on sheet A. So for example cell A1 might contain the text A, C, D. How do I re-write the macro second line to refer to the text in cell A1 in sheet A instead of the fixed values in the current macro?

Question 2
On Sheets B, C and D I have 8 named ranges (Report1, Report2...etc, all non-identical names). Before running the macro in question 1 how can I use vba to reset the print areas on each of these pages to the named ranges as defined in cell A1 on that sheet. For example, sheet B might have in cell A1, Report1, Report 3, Report 8 and these are the only ranges that should print from that sheet...

Thank you in advance, any help sincerely appreciated.

I have 10 reports (each in a sheet), and a user box with 10 checkboxes. User checks any combination of the 10 reports and then clicks on "Print Selected Reports". I need help writing a macro that would take every report checked and send them together to Print Preview.

I tried using the If Then Else structure, but I think it's pretty much impossible to account for every combination of reports. Basically the macro must take all of the checked reports and add them to an array where it looks like this:

Code:

Sheets(Array("APOD 1-Year", "Income Statement", "Lender Summary")).Select
ActiveWindow.SelectedSheets.PrintPreview


But i need help figuring out how to add each report to the array. Or maybe there is a better way? Any help is really appreciated!


I am a really a dummy to excel. Unfortunately, i am now facing an issue that makes me feel due to my knowledge and skills in excel are bad so i really need your help:

Let's say i have an excel file containing 4 sheets named as follow: sheet A, sheet B, sheet C, sheet D. My work requires me to print sheets out for 4 reports (report1,

report2, report3, report4) as given orders such as:

report1: print sheet A, then sheet B, sheet C, sheet D
report2: print sheet B, then sheet C, sheet D, sheet B
report3:........
report4:.............

What i think about to solve this issue is to make command button for each report. But i dont know how to write VBA code to get it work. Really need your help. All

comments will be appreciated. Thank in advance


Hi there,

I have a macro that does the following bit of code a number of times:


'Print Vic Consolidated
Sheets("New Template").Select
Range("h4").Select
ActiveCell.FormulaR1C1 = "29"
Sheets("new KPI").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=8, Collate:=True

Note: The ActiveCell.Formula is a variable, and changes from 1 to 29 according to the report that is run. The macro just changes the number to print out groups of reports (eg. 4, 5 , 6, 7 and 8 )


The thing I want to know is:

Can I make the number of Copies a variable as well???

As in, is it possible to do some sort of drop down menu when someone pushes the inital Print Macro button...how do I make it ask how many copies to print?

.......how do I build this in???


Thanks

Greg


Hi, I've inherited a workbook with macros to Get Data, Consolidate Data and Produce Reports. The Produce Reports macro creates and populates up to 9 Report worksheets according to a True/False table in a seperate worksheet, but first requires that any existing Report worksheets are deleted.
I'm looking to write two macros:

1) To delete any existing Report worksheets.

I've tried
Sheets("Report1").delete
Sheets("Report2").delete
etc

However, this brings up the 'The selected sheets will be permanently deleted' dialogue box for each deletion, and gives an error message if any of the sheets to delete don't exist. How can I automatically accept/ignore the 'selected sheets' dialogue, and then ignore any non-existing sheets?

I'd like to write it as:
If (this sheet) exists Then delete (this sheet)
but my syntax is lacking.

2) Print any existing Report worksheets

This is fine using

Sheets("Report1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Report2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
etc

until it comes across a non-existing report. Like in (1), I'd like to write
If (this sheet) exists Then print (this sheet)

Anyone help?


Hi,

Current situation:
I have 4 reports that I print out (transaction summary, evaluations, performance, etc...)
Once I print these reports for 250 clients, I then spend a lot of time packaging these accounts. I check for the clients name and need to manually group these reports in one package.

Is there a way to print these reports (memory or other method) and then make access sort them ( one client may not have 4 reports-may have only 3 reports), and print them the order I want?

Please guide me.

Thanks


Can anyone give me some advice on a neater way to organize my spreadsheet
data?

Here is the scenario.
I print out several different data reports. Each report contains 2 to 7
columns. The reports are printed on a monthly basis and give statistical
information. I would like to show the trends over a period of time by month.

For example, I have one report called "Enhanced System" and within that
report are 2 columns columns called Queue Length and Processes. Then I have
another report called "Performance" and that report has 3 columns called
Memory, Disk, and CPU.
Each report has only 2 records of data, one record for each server monitored.

I was going to create one column on the left with each row for a column from
the report then go horizontally with headers as months however the problem is
that I also need to know which report each row goes with. In the example I
only gave 2 reports but in truth there are 21 reports with number of
statistical columns varying.

Sept Oct Nov Dec
report1 column1
report1 column2
report2 column1
report2 column2
report2 column3



If any suggestions come to mind PLEASE let me know. Thanks.



I am using Windows XP and Excel 2007.
I have a workbook with 12 sheets named Rep1 to Rep12.
Each sheet contains a report. I have a userform ask the user which combination of reports they would like to save as pdf.
On the userform, the user can select any one report or any two reports or any three reports etc up to all 12 reports.
Once all sheets are selected I have a macro save to pdf.
The macro works ok provided I have a separate line of code for each possible combination.
e.g. To select reports(sheets) 1,2,3,4,5,6,11,12 I need the following code (R1, R2 etc represent the reports selected on the userform):
Private Sub OKButton_Click()
If R1 And R2 And R3 And R4 And R5 And R6 And R7 = False And R8 = False And R9 = False And R10 = False And R11 And R12 Then
Sheets(Array("REPORT1", "REPORT2", "REPORT3", "REPORT4", "REPORT5", "REPORT6", "REPORT11", "REPORT12")).Select
End sub
This is not efficient code because I will need hundreds of lines of code to cater for different combinations of reports.
What I really need is code to select a variable number of sheets.
Many thanks


Hi,

Could you please help me with a macro that will set the Print Area of a report based on the current lenght and width.

I have found macros that will set the Print area for reports of variable lenght but nothing for variable width.

I would love to be able to run the macro on the Active Sheet as I have several reports across a number of sheets.

Since the report is customised for the USER columns A:C are hidden as well as a large number of rows.

Your help is appreciated.

d


Good evening everybody.

I am working on a spreadsheet that has several sheets that are formatted for printing. I have the print area's set up with named ranges and the formatting looks good.

If the user prints 1 sheet only everything is OK, but I have one macro that I am working on to enable the users to select multiple sheets to print.

I am using the printpreview command so they can check the formatting before the actual print. There are buttons with macros on each tab with code like this:

Code:

Sub Print_Order
   Range("Sheet1_Print").PrintPreview
End Sub


This works great, but I would like to let the user choose multiple areas from different sheets and print it at one time. I have a method in place to select the various ranges, but I can not get them to print.

I tried setting a "Print_All" named range like this:

"Print_All" = "Sheet1_Print","Sheet2_Print","Sheet3_Print"



But i get yelled at by excel because of a global range error (The code is in a Module, not attached directly to one of the sheets)

The idea is that while looking at the preview they could view all pages before confirming that they did want to print.

I also tried something like this
Code:

Sheets("Sheet1").Select
Range("Sheet1_Print").select
Sheets("Sheet2").Select
Range("Sheet2_Print").select
Sheets("Sheet3").Select
Range("Sheet3_Print").select
Selection.PrintPreview


But all that it previews is the last page selected (all of them are highlighted however)

Any Ideas? I am sure it is something relatively simple but I am just brain dead right now

Thanks


Lee


Peace to you all
I already have a code to printpreview multiple ranges
VB:

Private Sub CommandButton1_Click() 
    Sh = "Sheet1" 
    A = Sheets(Sh).Range("J6") 
    B = Sheets(Sh).Range("K6") 
    C = Sheets(Sh).Range("M6") 
    D = Sheets(Sh).Range("N6") 
     ' to always put the first row in the printpreview
    Sheets(Sh).PageSetup.PrintTitleRows = Sheets(Sh).Rows(A).Address 
     ' to always put the first column in the printpreview
    Sheets(Sh).PageSetup.PrintTitleColumns = Sheets(Sh).Columns(B).Address 
     
     ' to put multiple columns in the printpreview
    Sheets(Sh).Range(C).PrintPreview 
    Sheets(Sh).Range(D).PrintPreview 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



It printpreviews only the first Range ( C )
I need it to print previews Ranges ( C ) and ( D ) or more in one page
I hope I'm clear enough
Any help'd be highly appreciated

What I am trying to do in the example below is print 3 tabs; "Info", "Last8" and "Graph" into one CutePDF .pdf file. I am using the path from the worksheet as a Dim as well as a name set in L6. This seems to work except it prints all the information on all tabs. It is not limiting the print range from A1 - L211 as I thought I specified. Actually I only need the range from A1 - L211 on the "Info" tab. The others are fine to print everything. Any help would be appreciated. And I am a vba newb, I got this by mixing and matching code bits I found. And it 'almost' works, lol. I am using Excel 2007. Thanks for any and all assistance

VB:

Sub Print3to1() 
     '
     '
    Print3to1 Macro 
     '
    Application.ScreenUpdating = False 
    Range("L6").Select x = ActiveCell.Value 
    Dim ThisWBPath As String 
    ThisWBPath = ThisWorkbook.Path 
    Sheets(Array("Info", "Last8", "Graph")).Select 
    Sheets("Info").Activate 
    ActiveSheet.PageSetup.PrintArea = "$A$1:$L$211" 
    ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="CutePDF Writer", PrToFileName:=ThisWBPath & "\" & x & ".pdf" 
    Application.ScreenUpdating = True 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




I am looking for a macro which says when this button is clicked activate this sheet print this range (page 1/ 30) then this range (2/30 then this range (3/30) activate sheet2 print this range (4/30) then this range (5/30) then this range (6/30) activate sheet3 etc etc while maintaining a page number total in the footer so page. Furthermore, these ranges on the pages need to have repeating columns and rows in them. The first set will be the first range, the second range will have a few of the first range's rows and columns

**Note that dim x is a variable which defines the print setup

VB:

Sub Button40_Click() 
    [B] 'Projection1[/B]
     
    [B]Sheets("Projection").Select[/B] 
    [B]ActiveSheet.PageSetup.PrintArea = "B2:I48"[/B] 
    [B]With x[/B] 
    [B]End With[/B] 
     
     
    [B] 'Projection[/B]
     
    [B]Sheets("Projection").Select[/B] 
    [B]ActiveSheet.PageSetup.PrintArea = "B2:I70,L2:S48,V2:AL48"[/B] 
    [B]With x[/B] 
    [B]End With[/B] 
    [B] 'Summary[/B]
    [B]Sheets("Summary").Select[/B] 
    [B]ActiveSheet.PageSetup.PrintArea = "B2:AB45"[/B] 
    [B]With x[/B] 
    [B]End With[/B] 
    [B]End Sub [/B] 
     
    [B] 'Title Page[/B]
    [B]Sheets("Title Page").Select[/B] 
    [B]ActiveSheet.PageSetup.PrintArea = "B2:F58"[/B] 
    [B]With x[/B] 
     
    [B]Workbook.Printout copies:= 1 [/B] 
     
    [B]End With[/B] 
     
    [B]End Sub [/B] 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Thank you for the help in advance!

Let me know if you have any further questions.

Hi--

I have 12 print areas set up on Sheet2. I don't always have info on all 12. I want something that will print out only the pages that info on them. Here is what I have right now, but it will only print out the last page. I'm guessing it starts at the bottom and goes up.
VB:

Sub Print1() 
    If Range("DJ78").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$1:$DI$78" 
    End If 
    If Range("DJ167").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$90:$DI$167" 
    End If 
    If Range("DJ256").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$179:$DI$256" 
    End If 
    If Range("DJ345").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$268:$DI$345" 
    End If 
    ActiveWindow.SelectedSheets.PrintPreview 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Can anyone help me fine tune this? So far the VBA is only set up for 4 pages, even though I will end up needing 12.



Hi,

I currently have an Excel 2010 macro that filters my data and prints multiple reports. This macro works pretty well except that it also prints reports with no data; thereby wasting paper. I am a novice in writing code, and I am unable to find what I need on the internet to avoid print dataless reports. If it helps, cell B2:C2 does contain a Subtotal that counts the number of filtered rows. Below is the portion of code that filters and prints a single report. Can anyone help?

Thanks much!

Pecan

' PROPOSALS - OPENED & PENDING
Range("F1:M1").Select
' CREATE - REPORT HEADING
ActiveCell.FormulaR1C1 = "Proposals Opened Since Last Report"
' FILTER ON STATUS SUMMARY DATE AND STATUS TYPE COLUMNS
ActiveSheet.Range("$A$5:$AC$775").AutoFilter Field:=3, Criteria1:="<>"
ActiveSheet.Range("$A$5:$AC$775").AutoFilter Field:=12, Criteria1:= _
"<>Expired", Operator:=xlAnd, Criteria2:="<>Closed-*"
' SELECT PRINTER & PRINT
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
' UNFILTER DATA
ActiveSheet.ShowAllData

I have a Sub-routine which determines which report to print based on user-input. I store the name of that report in a variable that is a string. My Reports are Numbered 1-12 and so I identify the report in question

e.g.

Dim c As Integer
Dim RName As String

c = Forms!Form1!text1
RName = "Report" & c

Now, it seems that I can use the variable RName to open the report, print, etc...however, if I need to change properties of the report, it doesn't seem to work.

For example,
Reports!Rname.RecordSource = "NewQuery" does not work.

Any suggestions as to how I might get around that...I was playing with declaring 'RName' as a Report instead of a String, but couldn't seem to get it to work.


Hi I am using excel 2000

I have the following code for printing each Area Managers Individual store reports

Code:

Sub CPSECompletedQuestionnairesPrintAreaManagersIndividualStores()
    Dim cell    As Range
    Dim sName   As String
        
    sName = InputBox("Enter Initials (Capitals) of AM Area you want to Print (CM, DB, ES, JD, JE, LH, LM, LMC, SC, SD or SM)")
    
    With Worksheets("Printing")
        For Each cell In .Range("B17", .Range("B17").End(xlDown))
            If .Cells(cell.Row, "a").Value = sName Then Sheets(cell.Text).PrintOut
        Next cell
    End With
End Sub


Each Individual store report prints out on 4 sheets of paper, how can the above code be changed so that the sheets print in reverse order

It will save me having to collate it all by hand, there are 78 stores x 4 sheets


I have a workbook with 25 sheets - 20 sheets are similar and require the PRINT AREA to be set the same in each sheet. I cannot group them as the Option to SET PRINT area is then not available in 2007. I have used this code but it sets the print area on all the work sheets in the workbook. I have tried to alter the code myself to only include those sheets that I have selected but have had no success.

VB:

Option Explicit 
Sub PRINTAREASETALL() 
    Dim strPA As String, Sht As Worksheet 
    On Error Goto NOT_RANGE 
    strPA = Selection.Address 
    For Each Sht In ActiveWorkbook.Worksheets 
        Sht.PageSetup.PrintArea = strPA 
    Next 
    Exit Sub 
    NOT_RANGE:  MsgBox "Select the Print Area Cells, then try again!" 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Any help would be appreciated.

Hi all,

I'm using Excel 2007 and working on some printable reports from a worksheet.

The worksheet is a detailed snapshot of information from a large table of data.

Consider I had 100 unique ID's in the table for each row and the report shows detail on 1 of these rows in a different worksheet.

Is there a way I could set it up to print through a number of rows from the table without having to have loads of worksheets?

Basically, say something like 'print reports from ID's 40-50'? or 'print reports that [Meet a particular Formula]'

Is there anyway to do this?

Thanks in advance


I need to print two versions of one spreadsheet. One version needs to print
A1:M98. The other version needs to print A1:Z132. I have the following
macro for A1:M98 -
Sub Print_1()
'
' Print_1 Macro
' ActiveSheet.PageSetup.PrintArea = "$A$1:$m$98"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A1:m98").Select
End Sub

Can I add another button to print A1:Z132, so I do not have to re-set the
print area every time?



I'm looping through a number of reports, and the print area is going to vary, depending on how many columns of data I have present. The starting cell will always be A1, but sometimes the print area will end at D1, E1, F1, etc.

Is there a way to use VBA to set the print area for each report, by refering to a worksheet containing the correct # of columns of data present.

In worksheet "Count," cell A1 = 3. This would mean the print area of my report would be A1 to (A1+3).


I am using a 53x27 worksheet with a print macro designed by someone in my
office. The first column contains information I would like to print on each
of the 3 pages that prints out. This is how it looks now:

ActiveSheet.PageSetup.PrintArea = "$a$6:$n$42"
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.PageSetup.PrintArea = "$o$6:$aa$42"
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.PageSetup.PrintArea = "$a$46:$m$53"
ActiveWindow.SelectedSheets.PrintPreview

I want the a6:a42 range to print on each page along with the above print
areas.




Hi,

I'm working on a Userform that has 4 checkboxes and 2 command buttons. Based on the checked value of the checkboxes I pdf the selected worksheets and then ask to print, else I cancel out of the checkbox and return to the worksheet from where I invoked the custom dialog sheet

Can you please pinpoint the error that I am making?

Here is the code

On a Worksheet, when a particular command button is clicked, I show the user form
Private Sub SendtoPrnt_Click()

UserForm2.Show

End Sub

After selections are made on the user form:

Private Sub CommandButton1_Click()
Dim Answer As String
Dim i As Long
i = 0

Rem DialogSheets("UserForm2").Select

With ActiveDialog

If (ActiveDialog.CheckBoxes("Report1").Value = True And _
ActiveDialog.CheckBoxes("Report2").Value = True And _
ActiveDialog.CheckBoxes("Report3").Value = True) Or _
(ActiveDialog.CheckBoxes("RptAll").Value = True Or _
ActiveDialog.CheckBoxes("RptAll").Value = False) Or _
(ActiveDialog.CheckBoxes("Report1").Value = False And _
ActiveDialog.CheckBoxes("Report2").Value = False And _
ActiveDialog.CheckBoxes("Report3").Value = False And _
ActiveDialog.CheckBoxes("RptAll").Value = True) Then
For i = 1 To 3
Worksheets("Prnt" & i).PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next i
ElseIf _
ActiveDialog.CheckBoxes("Report1").Value = True And _
ActiveDialog.CheckBoxes("Report2").Value = True And _
(ActiveDialog.CheckBoxes("Report3").Value = False Or _
ActiveDialog.CheckBoxes("RptAll").Value = False) Then
For i = 1 To 2
Worksheets("Prnt" & i).PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next i
ElseIf _
ActiveDialog.CheckBoxes("Report1").Value = True And _
ActiveDialog.CheckBoxes("Report3").Value = True And _
(ActiveDialog.CheckBoxes("Report2").Value = False Or _
ActiveDialog.CheckBoxes("RptAll").Value = False) Then
i = 1
Worksheets("Prnt1").PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
i = 3
Worksheets("Prnt3").PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
ElseIf _
ActiveDialog.CheckBoxes("Report2").Value = True And _
ActiveDialog.CheckBoxes("Report3").Value = True And _
(ActiveDialog.CheckBoxes("Report1").Value = False Or _
ActiveDialog.CheckBoxes("RptAll").Value = False) Then
For i = 2 To 3
Worksheets("Prnt" & i).PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next i
ElseIf ActiveDialog.CheckBoxes("Report1").Value = True Then
Worksheets("Prnt1").PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
ElseIf ActiveDialog.CheckBoxes("Report2").Value = True Then
Worksheets("Prnt2").PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
ElseIf ActiveDialog.CheckBoxes("Report3").Value = True Then
Worksheets("Prnt3").PrintPreview
Answer = MsgBox(Prompt:="Print this Page?", _
Title:="Print Confirmation", _
Buttons:=vbYesNo)
If Answer = vbYes Then
Call SaveToPDF
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
End If

End With

Unload Me

End Sub

When the command cancel is clicked (THis works!)
Private Sub CommandButton2_Click()
Unload Me
Worksheets("Other").Select
End Sub

Thanks for help!


So I've tried this a few different ways and every time I get the 1004 error: "Unable to set PrintArea property of PageSetup class"

Basically I have a macro that goes through a workbook to hide certain rows and columns based on some user inputs. After doing this I would like to set up appropriate print areas on certain sheets so that when you go to print anything it comes out clean. I thought this would be easy but I'm stuck.

The Plans variable in the code below is an integer from the user input. Think of this code as grabbing two separate boxes and setting them as print areas, which I've know I can do manually because I've tried it.

What's wrong with the following code?? (I've also tried using the union function here, to no avail)

Code:

Dim Region1 As Range
    Dim Region2 As Range
    
    Sheets("(2) Data Storage").Select
    Set Region1 = ActiveSheet.Range(Cells(1, 1), Cells(133 * Plans, 12))
    Set Region2 = ActiveSheet.Range("A2262:L2373")
    ActiveSheet.PageSetup.PrintArea = ""
    ActiveSheet.PageSetup.PrintArea = Region1
    ActiveSheet.PageSetup.PrintArea = Region2



Any ideas or guidance would be most appreciated. Thanks!