I am new to VBA so please bear with me. I have a workbook with several worksheets, each for a different month. The sheets are also named after the respective months. I recorded a macro in the sheet for April where I performed a Pivot Table on the data in that sheet. When I try to run that macro to make a similar pivot table on a different sheet e.g May or June, I get an error that reads, "Run-time error "1004": Application-defined or object-defined error".
This is the highlighted code,
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"April!R2C1:R1343C6", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="April!R4C9", TableName:="PivotTable4", DefaultVersion:= _
I realized that the code is only executing/making a pivot for "April" but I want it to make the macro able to run on any open sheet, Not just April. I suspect that this is the reason I am getting the error but correct me if I'm wrong.
So how can I make the macro work on any sheet or more dynamic instead of specific to the April sheet.