Warm Greetings & Happy Tuesday! 
I used Macro Recorder to create module that will insert a pivot table on a new worksheet titled "Invoice Pivot". The source worksheet will always be the same but the number of rows will change.
Issues I'm having with this code are:
A) How to specify worksheet should be added titled "Invoice Pivot" towards beginning
B) Range should reflect count # of rows for each wkst in future
C) Pivot cache - should this to be addressed in code? Read about it but not 100% clear
D) Future updates - should I worry about preventing table from updating each time source data sheet changes? If so, what verbiage can be included to manually update pivot table in future? Read in Excel Macros for Dummies
) about suspending automatic pivot table recalculations to prevent slowness.
Sorry if this sounds scattered; it's simply a reflection of my brain's condition in trying to understand Macros/VBA code! Totally new to this. Thanks in advance!
Sub CreatePVT()
'
' CreatePVT Macro
'
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Invoice!R1C1:R388C18", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice#")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("InvoiceDate")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("RequestAmount"), "Sum of RequestAmount", xlSum
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Invoice Pivot"
End Sub
I used Macro Recorder to create module that will insert a pivot table on a new worksheet titled "Invoice Pivot". The source worksheet will always be the same but the number of rows will change.
Issues I'm having with this code are:
A) How to specify worksheet should be added titled "Invoice Pivot" towards beginning
B) Range should reflect count # of rows for each wkst in future
C) Pivot cache - should this to be addressed in code? Read about it but not 100% clear
D) Future updates - should I worry about preventing table from updating each time source data sheet changes? If so, what verbiage can be included to manually update pivot table in future? Read in Excel Macros for Dummies
) about suspending automatic pivot table recalculations to prevent slowness. Sorry if this sounds scattered; it's simply a reflection of my brain's condition in trying to understand Macros/VBA code! Totally new to this. Thanks in advance!
Sub CreatePVT()
'
' CreatePVT Macro
'
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Invoice!R1C1:R388C18", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice#")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("InvoiceDate")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("RequestAmount"), "Sum of RequestAmount", xlSum
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Invoice Pivot"
End Sub
