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- Hide quoted text -
- Show quoted text -
Maggie,
Here is some code for you to test. I tried to comment the code for
your benefit, however, if you have questions please let me know. (I
will be honest though, this is the first time I have done any coding
with pivot tables). I created a mock-up pivot table in a workbook to
test this code and it seemed to work. I created the code as procedure
so that you can run it without having to fire the macro on the
workbook Open event. This way you can test the code without having to
open and close your worksheet many times.
I'm not sure if you are aware of the following or not (if you are,
then skip the paragraph): The F8 (Debug | Step Into) key will step
through code line by line and the F9 key will insert breaks (Debug |
Toggle Breakpoint) into the code (i.e. it will pause code execution on
the specified line). If you write "Debug.Print ..." into the code
window, it will write text to the Immediate Window (View | Immediate
Window). For example, after the line "For Each Wks In
ActiveWorkbook.Worksheets" you can insert a carriage return and write
Debug.Print "Worksheet Name:"; Wks.Name. As the code executes, it
will print the worksheet name into the Immediate Window. This is a
way of tracking what the program is doing.
Let me know if this helps.
Best,
Matt Herbert
Sub PivotTest()
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
Dim varShtArray As Variant
Dim Wks As Worksheet
Dim varWksMatch As Variant
'don't allow screen updating (this does NOT need to be set to TRUE
' at the end of the program; Excel will automatically set the
' property to TRUE at the end of the procedure's execution
Application.ScreenUpdating = False
'make the Pivot worksheet visible
Sheets("Pivot").Visible = True
'create an array with the worksheets that contain pivot tables to
' be updated
varShtArray = Array("Pivot", "Residential Data", "HELOC Data", _
"Commercial Data")
'loop through each worksheet in the workbook
For Each Wks In ActiveWorkbook.Worksheets
'use the match function to determine if the worksheet name
' matches a value in varShtArray; the variable is variant
' because the function will return an error if a match is
' not found
varWksMatch = Application.Match(Wks.Name, varShtArray, 0)
'proceed if varWksMatch is not an error (i.e. a match was found)
If Not IsError(varWksMatch) Then
'loop through each pivot table on the worksheet
For Each pvtTable In Wks.PivotTables
'refresh the pivot table
pvtTable.PivotCache.Refresh
Next
End If
Next
'when the screen updating returns, start the user on the
' Multifamily Data worksheet
Sheets("Multifamily Data").Select
'hide the Pivot worksheet
Sheets("Pivot").Visible = False
End Sub- Hide quoted text -
- Show quoted text -
I am new to this and I really do not understand the code you wrote up
there because I tried it and I got a error each time. I am not sure
what I am doing wrong. Please help!
Thanks,
Maggie
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.