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.

Alberto Ast wrote:
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

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" wrote:

Alberto Ast wrote:
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

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 wrote:
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" wrote:

Alberto Ast wrote:
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

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" wrote:

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 wrote:
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" wrote:

Alberto Ast wrote:
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

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

Alberto Ast wrote:
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" wrote:

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 wrote:
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" wrote:

Alberto Ast wrote:
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

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" wrote:

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

Alberto Ast wrote:
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" wrote:

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 wrote:
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" wrote:

Alberto Ast wrote:
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

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 wrote:
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" wrote:

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

Alberto Ast wrote:
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" wrote:

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 wrote:
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" wrote:

Alberto Ast wrote:
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

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

ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

Then I modified to

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Sheets("Datos").UsedRange.EntireColumn

and I get following error

Run Time Error 1004

The PT field name is not valid. To create a PT report, you must use data that is organazed as a list labeled columns. If you are changing the name of a PT field, you must type a new name for the fiels.

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.

"smartin" wrote:

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 wrote:
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" wrote:

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

Alberto Ast wrote:
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" wrote:

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 wrote:
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" wrote:

Alberto Ast wrote:
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