2

New to VBA and I am having difficulty with my Macro creating a pivot table on the same worksheet with my data (for example, starting the pivot table in the "I1" column). I have been able to run the macro to select all of the data within the sheet and then create this data on another worksheet. Since I will need to loop through a number of worksheets on the same workbook to create multiple pivot tables, having separate sheets isn't feasible.

Each worksheet has the same number of columns with a varying number of rows. My goal is to have this macro look at each worksheet and output a pivot table next to it.

I feel like the macro I have below is not referencing the correct pivot Table destination. Any assistance would be appreciated.

Sub Macro4() ' 'Macro4 Macro ' ' FinalRow = Cells(Rows.Count, 1).End(xlUp).Row DataSheet = ActiveSheet.Name ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ DataSheet & "!R1C1:R" & FinalRow & "C8", Version:=xlPivotTableVersion15). _ CreatePivotTable TableDestination:=DataSheet & "!R1C9", TableName:= _ "PivotTable4", DefaultVersion:=xlPivotTableVersion15 Sheets(DataSheet).Select Cells(1, 9).Select With ActiveSheet.PivotTables("PivotTable4").PivotFields("Document Type") .Orientation = xlRowField.Position = 1 End With With ActiveSheet.PivotTables("PivotTable4").PivotFields("Accounting Event") .Orientation = xlRowField.Position = 2 End With With ActiveSheet.PivotTables("PivotTable4").PivotFields("Document Number") .Orientation = xlRowField.Position = 3 End With ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables ( _"PivotTable4").PivotFields("Amount"), "Sum of Amount", xlSum End Sub
Improve this question
7
  • I believe your trouble is coming from this line: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ DataSheet & "!R1C1:R" & FinalRow & "C8", Version:=xlPivotTableVersion15). _ CreatePivotTable TableDestination:=DataSheet & "!R1C9", TableName:= _ "PivotTable4", DefaultVersion:=xlPivotTableVersion15 Sheets(DataSheet).Select - Why are you trying to select it? I think the format of the TableDestination is off. Commented Aug 24, 2016 at 17:21
  • I am trying to select the an ambiguous data set each time. Each worksheet has a different row length. So I thought the stated above would in some sense select all data on the active workbook. Commented Aug 24, 2016 at 17:37
  • I mean, that line creates a pivot table, but in the destination, you're trying to .Select something? Does that line work for you at all, I'd think it'd throw an error. I recommend instead, first creating a Range variable to store the destination, then just use that in the TableDestination:= part (i.e. TableDestination := myDest) Commented Aug 24, 2016 at 17:40
  • I see what you are saying. You are right, that is the line that I am having an issue with. I expected that it would be an error with the TableDestination:= I am working on adding a range right now to see if that resolves my issue. Commented Aug 24, 2016 at 18:15
  • (Also, not sure if it happened when pasting to SO, but you have some errant spaces in there as well (...PivotFields("Document Type") .Orientation has a space, for instance). Commented Aug 24, 2016 at 18:24

1 Answer 1

Reset to default
2

Try the code below, it's with a little different approach, defining Range, PivotTable, PivotCache, and you can modify them easily according to your needs:

Sub Macro4() Dim FinalRow As Long Dim DataSheet As String Dim PvtCache As PivotCache Dim PvtTbl As PivotTable Dim DataRng As Range Dim TableDest As Range FinalRow = Cells(Rows.Count, 1).End(xlUp).Row DataSheet = ActiveSheet.Name ' set data range for Pivot Table Set DataRng = Sheets(DataSheet).Range(Cells(1, 1), Cells(FinalRow, 8)) ' conversion of R1C1:R & FinalRow & C8 ' set range for Pivot table placement Set TableDest = Sheets(DataSheet).Cells(1, 9) ' conversion of R1C9 Set PvtCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, DataRng) ' this line in case the Pivot table doesn't exit >> first time running this Macro On Error Resume Next Set PvtTbl = ActiveWorkbook.Sheets(DataSheet).PivotTables("PivotTable4") ' check if "PivotTable4" Pivot Table already created (in past runs of this Macro) On Error GoTo 0 If PvtTbl Is Nothing Then ' "PivotTable4" doesn't exist >> create it ' create a new Pivot Table in "PivotTable4" sheet Set PvtTbl = ActiveWorkbook.Sheets(DataSheet).PivotTables.Add(PivotCache:=PvtCache, TableDestination:=TableDest, TableName:="PivotTable4") With PvtTbl.PivotFields("Document Type") .Orientation = xlRowField .Position = 1 End With With PvtTbl.PivotFields("Accounting Event") .Orientation = xlRowField .Position = 2 End With With PvtTbl.PivotFields("Document Number") .Orientation = xlRowField .Position = 3 End With PvtTbl.AddDataField ActiveSheet.PivotTables( _ "PivotTable4").PivotFields("Amount"), "Sum of Amount", xlSum Else ' just refresh the Pivot cache with the updated Range PvtTbl.ChangePivotCache PvtCache PvtTbl.RefreshTable End If End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Bruce - it worked once I was able to add the Range in. Shai that definitely worked once I plugged it in. It does exactly what I need and look clean to boot. Thanks for all of the help!! not to add the loop... You guys have been a big help.
You're welcome, don't forget to vote and mark as answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.