CODEWITHSUNDEEP
excelpivot-tableexcel-2013vba
Rukgo
Rukgo

Reputation: 121

Excel VBA Pivot Table data source issue

I have the following code...

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "page!R2C1:R1981C43", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable5", DefaultVersion _
    :=xlPivotTableVersion15

How would i change it from "page" to pull for any current worksheet name that I want to attempt.

For example if I run this on a worksheet named "pages" it gives me an error, how would I change it to accept any worksheet name?

Upvotes: 1

Views: 186

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

Just declare a variable and use that:

Dim myWorksheet as Worksheet
Set myWorksheet = Activesheet

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    myWorksheet & "!R2C1:R1981C43", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable5", DefaultVersion _
    :=xlPivotTableVersion15

This is untested, but I'm pretty sure that should work.

Upvotes: 1

Chris Shupe
Chris Shupe

Reputation: 1

Dim wbpage as worksheet

set wbpage = thisworkbook.sheets(1) 'or whatever

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    wbpage.name & "!R2C1:R1981C43", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable5", DefaultVersion _
    :=xlPivotTableVersion15

Upvotes: 0

Related Questions