pivot table variable range within a macro

[Alberto Ast]

Can I modify the pivot table range within a macro so once I update the table
it will pick up all the way to end of data?

What I do is to consider the table up to 10,000 rows but still I am limiting
to what ever I put in such number.

 

[smartin]

Can I modify the pivot table range within a macro so once I update the table
it will pick up all the way to end of data?

What I do is to consider the table up to 10,000 rows but still I am limiting
to what ever I put in such number.

Try this with the PT worksheet selected. Assumes source data on Sheet1.

Sub UpdatePT
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").UsedRange
'or, if you are like me, specify the entire column and never worry
'about it again
' ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").UsedRange.EntireColumn
End Sub

 

[Alberto Ast]

I could not make it to work... have a couple of questions...

My souce data sheet is different than my PT sheet... will this work with
your macro?
If I have several PT in same page how does the macro knows which table to
update...? I did not see a reference to a specific table.

 

[smartin]

It works in the somewhat limited case where the cursor is in the pivot
table to refresh, Sheet1 contains its data source, and Sheet1 is nicely
structured such that UsedRange supplies a range a pivot table can work
with.

In the code you will need to modify "Sheet1" with an appropriate
reference to your source data sheet.

However, if the source data sheet has multiple areas for various pivot
tables, or is not nicely structured, we will need to add code.

 

[Alberto Ast]

Still can not mate it to work.. I have the data in Sheet1 already used to
create the table... simple data table... then on Sheet2 I have the PT... I
have typed your macro and then execute it from Sheet2, I select the range on
Sheet1 and put the cursor inside the PT and then execute the macro but it
does not work.

Any thoughts?

 

[smartin]

Can you elaborate on "does not work"? Does this mean nothing happens? Do
you get an error message?

 

[Alberto Ast]

Before I run the macro this is the PT range
'3.6.6 RMA'!$A$7:$T$200

I can do refresh and it will work
Actual data goes farther than 200 rows

This is the macro base on your advise

Sub PT()
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Sheets("3.6.6 RMA").UsedRange.EntireColumn
End Sub

I put the cursor inside the PT before runing the macro.

 

[smartin]

Hi Alberto, could you tell me what happens when you run the macro? Do
you get an error? If so, what is the error?

 

[Alberto Ast]

This is the command I use and it works but it is limited to the current PT
range previously set up.

Then I modified to
Sheets("Datos").UsedRange.EntireColumn

and I get following error

Normaly I will get a label error when one column title is blank or duplicate
but it is not the case now... I can reflesh the PT with the above command
with no problem.

Thanks.