Macro Question, Importing data from a report & end value changes every time

[SatiricBecks]

Hi all thanks for any help offered.

I am trying to create a macro for a report that im doing.

at one point in the report i have to put the range field into a pivottable and pivot chart wizard.

which comes as 'rawdata'!$A$1@$I$1085 the end 4 digits (1085) will change reflecting the last row of data imported from the report, how would i get that to reflect in the macro?

Thanks again.

 

[BrightAndAlert]

Do you need a macro for this? How about changing the input range (to the last row in the sheet). This way you will not have to change your input range again. Filter out any blanks with filters in the pivot...

 

[RoryA]

You can use something like:

Activeworkbook.pivotcaches.create(xldatabase, "'rawdata'!" & sheets("rawdata").range("A1").currentregion.address(referencestyle:=xlR1C1))

 

[SatiricBecks]

Do you need a macro for this? How about changing the input range (to the last row in the sheet). This way you will not have to change your input range again. Filter out any blanks with filters in the pivot...

It is only one part of a long process, to change the input range, what would i have to do?

currently a box comes up and i type 'rawdata'!$A$1:$I$1085 i normally only change the 1085 to correspond with the last row of the file, is there something else i could put in instead of the 1085?

thanks again

 

[SatiricBecks]

It is only one part of a long process, to change the input range, what would i have to do?

currently a box comes up and i type 'rawdata'!$A$1:$I$1085 i normally only change the 1085 to correspond with the last row of the file, is there something else i could put in instead of the 1085?

thanks again

 

[RoryA]

If you just want to use all the data on rawdata then the code I posted should remove the need for any user input at all.

 

[BrightAndAlert]

Change 1085 to the last row in the sheet. I.e. 1.048.576 if you are using excel 2010.

It is only one part of a long process, to change the input range, what would i have to do?

currently a box comes up and i type 'rawdata'!$A$1:$I$1085 i normally only change the 1085 to correspond with the last row of the file, is there something else i could put in instead of the 1085?

thanks again