I have a worksheet that is constantly changing and I want my pivot
table to change when my worksheet changes automatically without having
to hit the refresh button. Is there a macro out there that will do
that?

On Mar 19, 11:14*am, Maggie wrote:
I have a worksheet that is constantly changing and I want my pivot
table to change when my worksheet changes automatically without having
to hit the refresh button. *Is there a macro out there that will do
that?

Maggie,

If you run the macro recorder to get the code for a pivot table
refresh you will get something similar to the following:

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

You can have this run after your "worksheet changes automatically."

Best,

Matt Herbert

On Mar 19, 12:30*pm, wrote:
On Mar 19, 11:14*am, Maggie wrote:

I have a worksheet that is constantly changing and I want my pivot
table to change when my worksheet changes automatically without having
to hit the refresh button. *Is there a macro out there that will do
that?

Maggie,

If you run the macro recorder to get the code for a pivot table
refresh you will get something similar to the following:

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

You can have this run after your "worksheet changes automatically."

Best,

Matt Herbert

That did not work when I tried that. Is there anything else that is
possible?

On Mar 19, 6:55*pm, Maggie wrote:
On Mar 19, 12:30*pm, wrote:





On Mar 19, 11:14*am, Maggie wrote:

I have a worksheet that is constantly changing and I want my pivot
table to change when my worksheet changes automatically without having
to hit the refresh button. *Is there a macro out there that will do
that?

Maggie,

If you run the macro recorder to get the code for a pivot table
refresh you will get something similar to the following:

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

You can have this run after your "worksheet changes automatically."

Best,

Matt Herbert

That did not work when I tried that. *Is there anything else that is
possible?- Hide quoted text -

- Show quoted text -

Maggie,

You would need to provide more detail on how your "worksheet changes
automatically." It's hard to determine how to help without more
detail. If you are unable to tie the pivot refresh to the procedure,
event, or other mechanism that changes your worksheet then the only
other solution I can think of right now is to set something up with
OnTime. VBE help has good documentation for this method.

Best,

Matt

On Mar 20, 2:44*pm, wrote:
On Mar 19, 6:55*pm, Maggie wrote:





On Mar 19, 12:30*pm, wrote:

On Mar 19, 11:14*am, Maggie wrote:

I have a worksheet that is constantly changing and I want my pivot
table to change when my worksheet changes automatically without having
to hit the refresh button. *Is there a macro out there that will do
that?

Maggie,

If you run the macro recorder to get the code for a pivot table
refresh you will get something similar to the following:

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

You can have this run after your "worksheet changes automatically."

Best,

Matt Herbert

That did not work when I tried that. *Is there anything else that is
possible?- Hide quoted text -

- Show quoted text -

Maggie,

You would need to provide more detail on how your "worksheet changes
automatically." *It's hard to determine how to help without more
detail. *If you are unable to tie the pivot refresh to the procedure,
event, or other mechanism that changes your worksheet then the only
other solution I can think of right now is to set something up with
OnTime. *VBE help has good documentation for this method.

Best,

Matt- Hide quoted text -

- Show quoted text -

Here is my macro but I am having issues with it and it will not auto
refresh.

Sub Macro2()
'
' Keyboard Shortcut: Ctrl+u
Sheets("Residential Data").Select
Range("W3:W103").Select
Sheets("Pivot").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
End Sub

On Mar 23, 1:37*pm, Maggie wrote:
On Mar 20, 2:44*pm, wrote:





On Mar 19, 6:55*pm, Maggie wrote:

On Mar 19, 12:30*pm, wrote:

On Mar 19, 11:14*am, Maggie wrote:

I have a worksheet that is constantly changing and I want my pivot
table to change when my worksheet changes automatically without having
to hit the refresh button. *Is there a macro out there that will do
that?

Maggie,

If you run the macro recorder to get the code for a pivot table
refresh you will get something similar to the following:

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

You can have this run after your "worksheet changes automatically."

Best,

Matt Herbert

That did not work when I tried that. *Is there anything else that is
possible?- Hide quoted text -

- Show quoted text -

Maggie,

You would need to provide more detail on how your "worksheet changes
automatically." *It's hard to determine how to help without more
detail. *If you are unable to tie the pivot refresh to the procedure,
event, or other mechanism that changes your worksheet then the only
other solution I can think of right now is to set something up with
OnTime. *VBE help has good documentation for this method.

Best,

Matt- Hide quoted text -

- Show quoted text -

Here is my macro but I am having issues with it and it will not auto
refresh.

Sub Macro2()
'
' Keyboard Shortcut: Ctrl+u
* * Sheets("Residential Data").Select
* * Range("W3:W103").Select
* * Sheets("Pivot").Select
* * ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh
End Sub- Hide quoted text -

- Show quoted text -

Maggie,

Is your pivot table expanding (i.e. are the rows or columns changing
in size)? The refresh will update data that has been changed within
an existing pivot table. For example, if your existing pivot table
SourceData is W3:W130 and then you add data such that your SourceData
is now W3:W200, then you have to "expand" your pivot table to
encompass the 70 rows that were added. In this scenario a Refresh
will simply update W3:W100, it won't capture the new data in W3:W200.
I'm anticipating that you will need to change the SourceData argument
of the pivot table. (One way is to create a named range for the data
set and then change the RefersTo of the named range as the rows are
added and then Refresh the pivot table. Or you could change the
PivotCaches(index).SourceData).

Turn on the macro recorder, create a pivot table, change some values
on the source data, perform a refresh, then add some new rows to your
existing pivot table, expand the source data, and then refresh again.
Lastly, turn the macro recorder off, and look at the code. This will
at least let you see some of the syntax behind a pivot table. (The
macro recorder adds a lot of code that doesn't really need to be
there; however, the recorder is doing its job by recording everything
you do).

Also, I don't typically like to use native Excel shortcut keys for my
macros. Ctrl + u performs underline and through setting your macro
key to Ctrl + u you'll lose the underline. (This can be reset with
the OnKey method, e.g. Application.OnKey "^u").

Let me know if this is helpful. (Keep posting and I'll check
periodically to assist with syntax, but first, I'm trying to help
define your issue before I know how to help you write the syntax).

Best,

Matt Herbert