Excel VBA Reference SourceData for Creating Pivot Table

1
votes

I need help!

In order to convert a table to a list I am using the following VBA formula. It was created by using record macro and the PivotTable Wizard (not the most elegant solution), but it works.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
    Array("PasteSheet!R1C1:R300C200"), Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
    DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").DataPivotField.PivotItems( _
    "Count of Value").Position = 1
ActiveSheet.PivotTables("PivotTable3").PivotFields("Row").Orientation = _
    xlHidden
ActiveSheet.PivotTables("PivotTable3").PivotFields("Column").Orientation = _
    xlHidden
Range("A4").Select
Selection.ShowDetail = True

My issue is I want to be able to set the SourceData to reference a stored variable as the source data range will change each time the macro is run, but I can't get it to work and googled everywhere with no result. My best shot was trying the following.

Dim newRange As Variant

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Set newRange = Selection

ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
    newRange, Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
    DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").DataPivotField.PivotItems( _
    "Count of Value").Position = 1
ActiveSheet.PivotTables("PivotTable3").PivotFields("Row").Orientation = _
    xlHidden
ActiveSheet.PivotTables("PivotTable3").PivotFields("Column").Orientation = _
    xlHidden
Range("A4").Select
Selection.ShowDetail = True

Help would be much appreciated!

2
vbaexcelpivot
pls. try with Dim newRange As Range - Karthick Gunasekaran

2 Answers

0
votes

You need to reference the source array differently. I am sure there is a better way than my example below, but this does work

Dim newRange As Range

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

newRange = Selection
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= Array("R" & newRange.Row & "C" & newRange.Column & ":R" & newRange.Row + newRange.Rows.Count - 1 & "C" & newRange.Column + newRange.Columns.Count - 1), Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="", TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").DataPivotField.PivotItems("Count of Value").Position = 1
ActiveSheet.PivotTables("PivotTable3").PivotFields("Row").Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable3").PivotFields("Column").Orientation = xlHidden
Range("A4").Select
Selection.ShowDetail = True
0
votes

I stumbled on this post as I was looking for a solution to a similar problem anyways. to answer to this old post. I might help someone else.

The SourceData can be addressed in two ways. either the one that witchild used:

"nameofyourworksheet!R1C1:R" & Nbroftherowsofthesourcedataorarray & "C17", Version:=xlPivotTableVersion15).CreatePivotTable _'

or, the 2nd way:

ActiveWorkbook.Worksheets("nameofyourworksheet").Range("A1:Q" & Nbroftherowsofthesourcedataorarray).Address(, , xlR1C1, True), Version:=xlPivotTableVersion15).CreatePivotTable _

The code line: .Address(, , xlR1C1, True) means that the range will be converted to the format "R..C.." to make it works. I didnt try it but I trust the forum :p

hope I was able to help people searching for a solution to this problem