On Mar 24, 11:42*am, wrote:
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- Hide quoted text -

- Show quoted text -

My data should not expand beyond the W130, it is just that the data in
that area is changing constantly. Here is my macro but I am still
getting issues with it because I have four pivot tables in one
worksheet that is linked to different worksheets and I still get an
error. Please help.

Sub Auto_Open()
Application.ScreenUpdating = False

Sheets("Pivot").Visible = True

Sheets("Pivot").Select

Range("W3:W130").Select

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

Sheets("Residential Data").Select

Range("W3:W130").Select

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

Sheets("HELOC Data").Select

Range("T3:T130").Select

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

Sheets("Commercial Data").Select

Range("T3:T130").Select

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

Sheets("Multifamily Data").Select

Sheets("Pivot").Visible = False

Application.ScreenUpdating = True

End Sub