VBA problem with Print Range code run-time error 1004 text not valid reference

[Jeep56]

The code below copies my workbook which has my data and code and, saves as "Air Hours with Month from data worksheet.
I then reopen the saved file and am trying to format the print area. My code keeps stalling at .PrintArea = "PRange"
I'm getting a run time error 1004 - Text entered is not a valid reference. I'm not sure what is wrong.

Please Login or Register  to view this content.

Could someone please help me out?

[Norie]

PRange is a variable in your code, it's not a named range.

[AlphaFrog]

Try this...

.PrintArea = PRange.Address

[Jeep56]

Thanks AlphaFrog, that works.

There must be something wrong with my print area though. The report I'm setting the print area for is a pivot table. It's only setting column A of the pivot table as the print area not the entire pivot table. Would you be able to help with that?

[AlphaFrog]

Thanks AlphaFrog, that works.

There must be something wrong with my print area though. The report I'm setting the print area for is a pivot table. It's only setting column A of the pivot table as the print area not the entire pivot table. Would you be able to help with that?

This is not tested.

Please Login or Register  to view this content.

[Norie]

You can get the range of a pivot table using the property TableRange1(TableRange2 to include page fields).

[Jeep56]

I'm getting an error 91 object variable or with block not set at line FinalRow = wks.Cells.Find("*", , , , 1, 2).Row
The report then appears and, it seems to have the print areas set correctly. Just need to get rid of the Error 91

[Norie]

You'll get that error if any worksheets are blank.

[AlphaFrog]

Please Login or Register  to view this content.

[Jeep56]

Hi Norie

How would I code this? I keep making pivot tables as they're so useful but, I have great difficulty trying to refer back to them.

Dim Pt as pivottable
.PrintArea = Pt.tableRange2.address

[Jeep56]

Thanks for your help. I was getting the error code 91 because I had an empty worksheet. Your initial code worked AlphaFrog bit I will add in the "IF" statement as an extra percaution.

10 months ago, I'd never heard of VBA...I have learned so much from this forum

[Norie]

You would need to set Pt to the pivot table.

Also, you probably don't want to use the exact address of the TableRange2 for the print area.

For example, if there was only one pivot table on Sheet4 this code would set the print area from A1 to the bottom right corner of the pivot table.

Please Login or Register  to view this content.

[Jeep56]

Thank you!