happydonut
Board Regular
- Joined
- Nov 28, 2019
- Messages
- 57
- Office Version
- 365
- Platform
- Windows
Hi,
I have a macro to create pivot table, however im getting the error "This command requires at least two rows of source data." when trying to run it when all rows are populated. Here is my code:
These lines gets marked in yellow:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R9C1:R" & Last_Row & "C7", Version:=6).CreatePivotTable TableDestination:= _
"Results!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Any suggestions how to fix it?
Appreciated
I have a macro to create pivot table, however im getting the error "This command requires at least two rows of source data." when trying to run it when all rows are populated. Here is my code:
VBA Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Dim Last_Row As Long
Last_Row = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets.Add.Name = "Results"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R9C1:R" & Last_Row & "C7", Version:=6).CreatePivotTable TableDestination:= _
"Results!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Results").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount IN"), "Sum of Amount IN", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount OUT"), "Sum of Amount OUT", xlSum
End Sub
These lines gets marked in yellow:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R9C1:R" & Last_Row & "C7", Version:=6).CreatePivotTable TableDestination:= _
"Results!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Any suggestions how to fix it?
Appreciated