Macro to Re Set Print Area

[Simonc64]

All

I have found some answers to this from 13 years ago but none seem to do what I'm after.

I have built a pivot table report, that refreshes and formats on the press of a button with macro attached and it works really well. The icing on the cake would be for that macro to also reset the print area every time I refresh the pivot.

The width of the report will never change just the length, as more records are added to the report. Is there any code that I could use to make this happen?

Thanks in advance for all your help!

Simon

 

[Simonc64]

trying to sort this I came across this code

Sub SetPrintArea()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("3+ Analysis")
lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws.PageSetup.PrintArea = ws.Range("A1:J" & lastRow).Address
End Sub


Tried attaching to my existing macro and keep getting end sub expected error - anyone with VBA knowledge able to help on this code or the error message?