VBA | Pivot Table | Change Source Data | File Directory

dutch245

New Member
Joined
Jun 26, 2019
Messages
7
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.

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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,102,542
Messages
5,487,470
Members
407,602
Latest member
clang663

This Week's Hot Topics

Top