Changing the data source for pivot using VB

[Mannington]

I have a workbook, where I have named the sheet using a cell reference, eg 032013. I have then created a pivot table on a seperate sheet. Each month a new sheet is created, with the new sheet name, eg. 042013, 052013. I want to change the data source to the new sheet name each month. I seem to be having problems with the sheet name reference, the code below works, but if I try to give it a sheet name based on cell reference it does not work. Any help would be appreciated.

sub update

Sheets("Summary").Select ' contains the pivot table

Dim ptMyPivotTable as PivotTable

Set ptMyPivotTable = ActiveSheet.PivotTables("PivotTable2")

ptMyPivotTable.SourceData= Sheets("032013").Range("a1").CurrentRegion.Address(true, True, xlR1C1, True)

ptMyPivotTable.PivotCache.Refresh

end sub

 

[Marcelo Branco]

Maybe something like this

Assumes
Sheetnames always have 6 digits
Reference cell = Summary!G1 (adjust to suit)

Sub update()
    Dim sheetName As String, ptMyPivotTable As PivotTable
    
    With Sheets("Summary")
        sheetName = .Range("G1").Value '<--Adjust
        sheetName = Format(sheetName, "000000")
            
        Set ptMyPivotTable = .PivotTables("PivotTable2")
        ptMyPivotTable.SourceData = _
            Sheets(sheetName).Range("A1").CurrentRegion.Address(True, True, xlR1C1, True)
        ptMyPivotTable.PivotCache.Refresh
    End With
 
End Sub

M.

 

[Mannington]

Thanks so much for this, perfect

 

[Marcelo Branco]

Thanks so much for this, perfect

You ae welcome and thanks for the feedback.

M.