LastRow on Changing Pivottable

Hi guys

I've been lurking on here for a while and haven't really posted in the VBA section up to now as I've been able tofind the solutions that I want simply by searching.

However, I haven't had any luck with this query and am hoping somebody can help out.
Am using Excel 2007, only using VBA for the past 3weeks, and am an accountant not a programmer. However, i can say that I'm very good at using excel spreadsheets and know how to use sumproduct, array formulas etc.

I have a pivottable, and am using VBA to copy pivottable info across to an added sheet.
The pivottable will have 3 separate states (each state has a different number of fields that are displayed) that will need to be copied across to the one single sheet.
I'm writing the macro to copy from Pivot sheet, paste into target, then switch back to pivot, adjsut pivot, copy, then paste into target and so on..

Pivot1 is the Pivottable
Julia_Inhouse is the Targetsheet

I've managed to copy across the first state of the pivot table by using this code:
You can see that I've used

Please Login or Register  to view this content.

So far so good. The info pastes into the target sheet.
Now I need to go back to the pivottable and add another field.
This is the Pivottable in the second state, whcih has more info than the first.
One additional column and more rows.
So i've changed the column ref to "F", but I've left the LastRow4 reference the same.
However, when I run the macro, the LastRow4 reference still refers to the lastrow when the Pivottable was in its first state.

Please Login or Register  to view this content.

My question is how to update the LastRow formula so that I can capture all info from A3 to lastrow (including grand totals) when pivottable is in its second state.

Will the solution to this will suffice for when the pivottable is in its 3rd state i.e. after another field is added, and i need to copy the whole pivottable contents to sheet Julia_inhouse.

Alternatively, can you suggest another way of selecting the pivottable data from A3 to the last row.

Note that the xlLast cell function cannot be used.
This is because the pivottable is used first before the macro is to be run.
When the pivottable is used prior to any macros, it displays all fields of data which results in the excel last cell being 1,000s of lines down. Then when the macro is being run, the xlLastCell will be on a row far below the last line of each of the 3 states of the pivottable that I want to copy to new sheet.

Thanks.

The pivot table object has various range references you can use to determine its location/coveage.

Please Login or Register  to view this content.

Object browser and help will provide more info.