I'm trying to write a macro which will create a pivot table for a report.
I have tried to make the range of the report data dynamic, as the number of rows of the report will vary based on when it is run.
My data starts at cell A1 every time.
I have spent over a week poring over different codes, learning about Objects and Properties, and generally trying to achieve something which I really think shouldn't be this difficult. Unfortunately I'm a rookie when it comes to macros.
When I run my code, I receive Error 1004 regarding my 'pvtable' line.
I would just like a macro which creates a PivotTable on a different worksheet within the same workbook. Please would anyone be able to advise where I'm going wrong?
Please see below code:
Sub Pivot_Table()
'Declare Variables
Dim pvtable As PivotTable
Dim pvcache As PivotCache
Dim pvsheet As Worksheet
Dim pdsheet As Worksheet
Dim plr As Long
Dim plc As Long
Dim StartPvt As String
Dim ScrData As String
Worksheets.Add After:=ActiveSheet
Set pvsheet = Worksheets(3)
Set pdsheet = Worksheets("IkenReport")
'Define Data Range
plr = Worksheets("IkenReport").Cells(Rows.Count, 1).End(xlUp).Row
plc = Worksheets("IkenReport").Cells(1, Columns.Count).End(xlToLeft).Column
ScrData = ActiveSheet.Name & "!" & Range(Cells(1, 1), Cells(plr, plc)).Address(ReferenceStyle:=xlR1C1)
StartPvt = Sheets.Add.Name & "!" & Sheets.Add.Range("A3").Address(ReferenceStyle:=xlR1C1)
Set pvcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ScrData)
Set pvtable = pvcache.CreatePivotTable(TableDestination:=ActiveWorkbook.Worksheets(3).Range("A4"), TableName:="MonthlyReport")
With pvtable
.PivotFields("Time Recorded By").Orientation = xlRowField
.PivotFields("Case Client").Orientation = xlColumnField
.PivotFields("Time Recorded Hours").Orientation = xlDataField
End With