Dynamic Print Range With Print Preview

[Pete0922]

New To VBA

I wrote a macro to print a specific range on a specific sheet:

Sub PrintPreview()
If ActiveSheet.Name <> "Sheet2" Then _
Sheets("database").Range("C1:AE12").PrintPreview
End Sub

However, the table I am attempting to print will have additional rows added (number of columns will remain constant). So I then created a dynamic range called "Print_Area". However, I can't figure out how to incorporate the dynamic range into my macro.

Thanks in advance

 

[alansidman]

[COLOR=#333333]Sheets("database").Range(Print_Area).PrintPreview[/COLOR]

if you created as a vba variable

[COLOR=#333333]Sheets("database").Range("Print_Area").PrintPreview[/COLOR]

if you created using the name manager

 

[Pete0922]

Alan,

Thanks for the quick reply. Unfortunately, I get an error when I run it.

First of all; I gave you some bad info, the dynamic range is called "PivotTable" not "Print_Area".

This is what the macro looks like now:

Sub PrintPreview()
If ActiveSheet.Name <> "Sheet2" Then _
Sheets("database").Range("PivotTable").PrintPreview
End Sub

When I run the macro, I get the following error message: "Run time error; 1004, Application defined or object defined error.

Thanks.

 

[alansidman]

So, "PivotTable" is a named range and not a variable in VBA?

 

[Pete0922]

Correct. The named range was created with a formula.

 

[alansidman]

The named range was created with a formula.

Please explain this as I don't understand how this happens. Ranges are usually named using the NameManager.

 

[Pete0922]

That'a correct. I named it PivotTable. Perhaps that was not a good choice(?)

 

[alansidman]

I could not replicate your issue. I built a sample workbook. Named a Range "PivotTable". Used your code and it showed the range as the print preview. So your code is fine, you have some other issue. Try creating a new workbook and use the Name Manager.

On the Ribbon, Select Formulas, Select Define Name and highlight your range. This is what I did and then ran your code.