Refresh data in Pivot table!

[Milksnatcher]

Hi, I am a complete VBA newcomer and I have a worksheet in which I want to refresh the data in a pivot table. The data is kept in several identical worksheets. I recorded a macro to change te data which worked fine.... but I wat to be able to change the spreadsheet name by a string name:

Sub Macro4()
'

sheet = Worksheets("Results").Range("b4")

Range("B8").Select

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"sheet!R1C2:R242C4"
End Sub

I have a validation list in Results!B4, which contains the names of all the sheets. No matter how I have tried to reference the data path I keep on getting it wrong. Can someone help me? The range of data will be the same, but the sheet name needs to change according to the validation list selection. I'm stumped.

 

[Andrew Poulsom]

Welcome to the Board.

I don't see any refreshing there. That code is creating a pivot table. You can set the SourceData like this:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'" & sheet & "'!R1C2:R242C4"

 

[Milksnatcher]

Hi, this was a macro that I recorded by going to the wizard and then changing the sourcedata. Can you let me know how to refresh it correctly?

 

[Andrew Poulsom]

The code I posted will work provided that the active cell is within a pivot table.

 

[Milksnatcher]

You are a star, thank you so much, I have wasted so much time trying to get this to work. You may end up with more posts as the project gets progressively more complex!