I have a pivot table that is linked to an access query for the data. I'm
attempting to get a macro that will refresh the data after some data in the
excel sheet is changed. The change is linked into a table in ms access and
the query uses that table to calculate. If I just hit refresh on the pivot
table, it works fine, but I can't seem to get a macro to work - i'm very new
to vba programming.
--
smiller600

You can use event code to refresh the pivot table. For example,
right-click on the sheet tab of the sheet where you'll make the change,
and choose View Code.
Where the cursor is flashing, paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot").PivotTables(1).PivotCache.Refr esh
End Sub

Change the sheet name to the pivot table sheet name in your workbook.
If you want the pivot table to refresh only when a specific cell is
changed, you could test for that in the code. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Then
Worksheets("Pivot").PivotTables(1).PivotCache.Refr esh
End If
End Sub


smiller600 wrote:
I have a pivot table that is linked to an access query for the data. I'm
attempting to get a macro that will refresh the data after some data in the
excel sheet is changed. The change is linked into a table in ms access and
the query uses that table to calculate. If I just hit refresh on the pivot
table, it works fine, but I can't seem to get a macro to work - i'm very new
to vba programming.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Will this work for the pivot cache coming in from an access query? I need
the query to update the data for the pivot table - When I've tried the below
type of code, I get the following run time error (1004) [Microsoft][ODBC
Microsoft Access Driver]Connection for viewing your linked microsoft excel
worksheet was lost.

When choose tools/references in visual basic - I get a error access system
registery.

I'm starting to link there is something wrong inside my microsoft not being
able to access something it needs.

--
smiller600


"Debra Dalgleish" wrote:

You can use event code to refresh the pivot table. For example,
right-click on the sheet tab of the sheet where you'll make the change,
and choose View Code.
Where the cursor is flashing, paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot").PivotTables(1).PivotCache.Refr esh
End Sub

Change the sheet name to the pivot table sheet name in your workbook.
If you want the pivot table to refresh only when a specific cell is
changed, you could test for that in the code. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Then
Worksheets("Pivot").PivotTables(1).PivotCache.Refr esh
End If
End Sub


smiller600 wrote:
I have a pivot table that is linked to an access query for the data. I'm
attempting to get a macro that will refresh the data after some data in the
excel sheet is changed. The change is linked into a table in ms access and
the query uses that table to calculate. If I just hit refresh on the pivot
table, it works fine, but I can't seem to get a macro to work - i'm very new
to vba programming.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html