ActiveSheet.PageSetup.PrintArea = variable

MagnaForce

New Member
Joined
Jul 1, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Need some help getting an area selected to become a variable. The reason is there are other areas on the sheet I do not want to print and I am going to be using print selected.

VBA Code:
    'Need to make this a variable
    With Sheets("CWS")
    .Range("A1:O" & .Range("B" & Rows.Count).End(xlUp).Row).Select
    End With

    'This is where I need the variable to be
    ActiveSheet.PageSetup.PrintArea = "Variable goes here"
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,993
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
  With Sheets("CWS")
    .PageSetup.PrintArea = .Range("A1:O" & .Range("B" & Rows.Count).End(xlUp).Row).Address
  End With
 
Solution

MagnaForce

New Member
Joined
Jul 1, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
It looks like it is going to work, but I got an error on "Application.PrintCommunication = True".
I had to manually select Adobe PDF as the printer and rerun the code and it works.

Do you know of anyway to have VBA automatically select Adobe PDF as the intended printer?
 

spencer_time

Board Regular
Joined
Sep 19, 2019
Messages
55
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
You could try one of the following two examples.

VBA Code:
Application.Dialogs(xlDialogPrinterSetup).Show

OR

VBA Code:
Sub Change_Default_Printer()
Set mynetwork = CreateObject("WScript.network")
mynetwork.setdefaultprinter "Your Printer Name"     'write your printers name here
ActiveSheet.PrintOut
mynetwork.setdefaultprinter “original_Default_Printer”  ‘set back your original printer to default”
End Sub
 

MagnaForce

New Member
Joined
Jul 1, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I could only mark one as a solution. although they were both a solution to my problem. I gave you both a thumbs up. Thanks for the quick response, you guys are great.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,993
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,148,324
Messages
5,746,121
Members
423,993
Latest member
Vighper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top