Pivot Table Code

[Michael M]

Hi All
This is my first post to this site. I used to post on the Office online site but my administrators decided to restrict my access.
My question is:
I would like the code below to refer the Pivot table to the current Active sheet NOT "'[TESTEquipInv_0506.xls]Oct05 WBS'!R6C20" as shown in the code.
Any advice would be appreciated
Regards
Michael M


Option Explicit

Sub PIVOT()
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"R1C6:R57C17").CreatePivotTable TableDestination:= _
"'[TESTEquipInv_0506.xls]Oct05 WBS'!R6C20", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
Application.CommandBars("PivotTable").Visible = False
With ActiveSheet.PivotTables("PivotTable2").PivotFields("WBS Element")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Allocation"), "Sum of Allocation", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("T6").Select
End Sub

 

[tactps]

Have you tried changing:
"'[TESTEquipInv_0506.xls]Oct05 WBS'!R6C20"
to
activesheet.name & "!R6C20"
or similar?

 

[Michael M]

Pivot table code

Hi Tactps

Thanks for the quick response.
You can probably tell that this was recorded via the recorder and I'm not a VBA expert. I tried a number of options, and yes, I did try your example. Unfortunately, I accidently removed the exclamation mark as well.

I have input your example, with the expected positive result.

Thanks again for your time and input

Regards
Michael

 

[tactps]

No problem - welcome to the board.
Always happy to help.