At the closing steps of a macro I'm writing I copy a sheet with a pivot table to the end of the workbook and rename it and the pivot table within it.
I want to change the data source to a new sheet, and when I recorded myself doing the action I have part of the code looking like the below.
The problem is that I need the DJL Data | 07.08!R1C1:R84C10 portion of that code to reflect the correct sheet and range.
Only the date in the sheet name will change, so the sheet format will always be DJL Data | XX.XX, and the range will change with each new DJL Data sheet.
Please let me know if this makes sense, and how I can adjust my code!
I want to change the data source to a new sheet, and when I recorded myself doing the action I have part of the code looking like the below.
Code:
ActiveSheet.PivotTables("PT | 07.08").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"https://xxx.xxx.com/xx/xxx/xxx/xxx/xxxx/xx/[Project Shift PRS V3.xlsm]DJL Data | 07.08!R1C1:R84C10" _
, Version:=6)
The problem is that I need the DJL Data | 07.08!R1C1:R84C10 portion of that code to reflect the correct sheet and range.
Only the date in the sheet name will change, so the sheet format will always be DJL Data | XX.XX, and the range will change with each new DJL Data sheet.
Please let me know if this makes sense, and how I can adjust my code!