I have been working on a project whereby I have a template sheet in excel (to leep track of daily time spent)

I used the macro screen recording function on excel to duplicate the sheet and update the source data for the new pivot tables per sheet.

Within the VBA code, it is directly linking to the file path, however, this means when i move the file or share it with others then this code needs to be manually updated.

I was trying to see if i could link to the “current active workbook” or make it dynamic, however, i wasn’t coming right (especially with the workbook being saved to a sharepoint site on One Drive”)

Is there any advice you could possibly give me please?

Using “active workbook links”

Here is my code:

Sub Add_New_Week()
'
' Add_New_Week Macro
'
' Unprotect the sheet
    Sheets("Template").Unprotect "Password"
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Sheets("Template (2)").Select
    Sheets("Template (2)").Name = "{RenameWeek}"
    Sheets("{RenameWeek}").Select
    Range("B34").Select
    ActiveSheet.PivotTables("PivotTable_Activity_0").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
         "https://za[companyname]-my.sharepoint/personal/dylan_r_rees_[companyname]_com/Documents/1. Timesheets/[2025 Timesheets.xlsm]{RenameWeek}!R4C59:R1048576C63" _
    , Version:=8)
    Range("E34").Select
    ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
          "https://za[companyname]-my.sharepoint/personal/dylan_r_rees_[companyname]_com/Documents/1. Timesheets/[2025 Timesheets.xlsm]{RenameWeek}!R4C59:R1048576C63" _
    , Version:=8)
    Range("CE6").Select
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ("PivotTable_Activity_0" _
        )
    Range("CQ6").Select
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ("PivotTable5")
    Range("A4").Select
   
' Protect the sheet again
    Sheets("Template").Protect "Password", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
    Sheets("{RenameWeek}").Protect "Password", UserInterfaceOnly:=True, AllowUsingPivotTables:=True

End Sub

I have been working on a project whereby I have a template sheet in excel (to leep track of daily time spent)

I used the macro screen recording function on excel to duplicate the sheet and update the source data for the new pivot tables per sheet.

Within the VBA code, it is directly linking to the file path, however, this means when i move the file or share it with others then this code needs to be manually updated.

I was trying to see if i could link to the “current active workbook” or make it dynamic, however, i wasn’t coming right (especially with the workbook being saved to a sharepoint site on One Drive”)

Is there any advice you could possibly give me please?

Using “active workbook links”

Here is my code:

Sub Add_New_Week()
'
' Add_New_Week Macro
'
' Unprotect the sheet
    Sheets("Template").Unprotect "Password"
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Sheets("Template (2)").Select
    Sheets("Template (2)").Name = "{RenameWeek}"
    Sheets("{RenameWeek}").Select
    Range("B34").Select
    ActiveSheet.PivotTables("PivotTable_Activity_0").ChangePivotCache _
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
         "https://za[companyname]-my.sharepoint/personal/dylan_r_rees_[companyname]_com/Documents/1. Timesheets/[2025 Timesheets.xlsm]{RenameWeek}!R4C59:R1048576C63" _
    , Version:=8)
    Range("E34").Select
    ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
          "https://za[companyname]-my.sharepoint/personal/dylan_r_rees_[companyname]_com/Documents/1. Timesheets/[2025 Timesheets.xlsm]{RenameWeek}!R4C59:R1048576C63" _
    , Version:=8)
    Range("CE6").Select
    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ("PivotTable_Activity_0" _
        )
    Range("CQ6").Select
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ("PivotTable5")
    Range("A4").Select
   
' Protect the sheet again
    Sheets("Template").Protect "Password", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
    Sheets("{RenameWeek}").Protect "Password", UserInterfaceOnly:=True, AllowUsingPivotTables:=True

End Sub

• excel
• vba

Share Improve this question Follow edited Feb 3 at 20:01 Dylan Rees asked Feb 2 at 20:30 Dylan ReesDylan Rees 111 bronze badge 11

• 2 Either ActiveWorkbook.Path or ThisWorkbook.Path is probably what you want. If neither of those work, then you should edit your post to explain why not and preferably add the code you need to adjust. – Tim Williams Commented Feb 2 at 21:28
• 1 as Tim said... ThisWorkbook ensures that the workbook where the vba code exists is referenced. – pgSystemTester Commented Feb 3 at 7:58
• Hi there @TimWilliams, thank you for the help. I did try use these references and it did work, however, when the file was saved on my desktop but I need to keep the file on my OneDrive which is stored through Sharepoint. – Dylan Rees Commented Feb 3 at 14:23
• In that case please show the code you're using. – Tim Williams Commented Feb 3 at 15:57
• @TimWilliams, I have added my code to my post – Dylan Rees Commented Feb 3 at 19:27

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 0

Untested, but this avoids having to include the path:

Sub Add_New_Week()
    
    Const PW As String = "Password"
    Const WK_NAME As String = "{RenameWeek}"
    Dim wb As Workbook, ws As Worksheet, pc As PivotCache, wsWeek As Worksheet
    
    Set wb = ThisWorkbook
    
    With wb.Worksheets("Template")
        .Unprotect PW
        .Copy After:=wb.Worksheets(wb.Worksheets.count)
        .Protect PW, UserInterfaceOnly:=True, AllowUsingPivotTables:=True
    End With
    
    Set ws = wb.Worksheets(wb.Worksheets.count)
    ws.Name = WK_NAME
    
    Set wsWeek = wb.Worksheets(WK_NAME)
    
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsWeek.Range("$BG$4:$BK$1048576"), Version:=8)
    With wsWeek
        .PivotTables("PivotTable_Activity_0").ChangePivotCache pc
        .PivotTables("PivotTable5").ChangePivotCache pc
        .PivotTables("PivotTable1").ChangePivotCache "PivotTable_Activity_0"
        .PivotTables("PivotTable2").ChangePivotCache "PivotTable5"
        .Protect "Password", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
    End With
   
End Sub