CODEWITHSUNDEEP
vbaexcelpivot
Wayne Zhang
Wayne Zhang

Reputation: 21

VBA Macro - Pivot Table CurrenRegion

I am keep getting an error message

Run-time Error 1004: Unable to get the CurrentRegion Property of the Range Class.

I am trying to create a dynamic Source Data, since the source is changing. 

Dim pc As PivotCache
Dim pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=Sheet1.Name & "!" & Sheet1.Range("A1").CurrentRegion.Address, _
    Version:=xlPivotTableVersion14)

Worksheets.Add
Range("A3").Select

Set pt = pc.CreatePivotTable( _
    TableDestination:=ActiveCell, _
    TableName:="NetZero")

Upvotes: 2

Views: 1158

Answers (3)

NAVEEN PRAKASH
NAVEEN PRAKASH

Reputation: 130

I don't know the exact reason but I was having the same problem. You can try selecting the cells in excel sheet. If you are able to select the cell then your CurrentRegion property will work. If not then your cells are locked. You reopen your excel sheet and try running the macro.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

If you wish to keep using the concept of using a dynamic range that grows (not a table), then use the code below:

Dim pc As PivotCache
Dim pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                    SourceData:=Sheet1.Range("A1").CurrentRegion.Address(False, False, xlA1, xlExternal))

Worksheets.Add

' there's no need to select the Range, you can set the Table destination to "A3"
Set pt = pc.CreatePivotTable(TableDestination:=Range("A3"), TableName:="NetZero")

Upvotes: 0

Volkan Yurtseven
Volkan Yurtseven

Reputation: 444

You don't need a macro for this. Just convert your source data to Table. Use "Format as Table" button on Home menu. and then your source data will always be dynamic.

Upvotes: 1

Related Questions