Set a network path in VBA for excel formulas

[ItalianPlatinum]

Hello,
I have multiple columns that reference the same network path. At times I may need to change that path but i dont want to go into my VBA every time and modify all the lines of code that use that path. I am looking for a way to set that path once then its used throughout the rest of the code. would someone be able to assist? here is my line of code i have to XXXX out the path due to confidential names in there.

Sub CopyFormulas()
Dim rws As Long
Dim UsdRws As Long
Dim LastRow As Long

'paste formulas down in Compare
With Sheets("Calc")
    .Unprotect
    .Range("D1:L" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "='\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Rate!D8"
    .Range("M1:M" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "='\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Dist'!K8"
    .Range("N1:N" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "='\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Dist'!O8"
    .Range("O1:O" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "='\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Dist'!S8"
    .Range("S1:S" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "='\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Dist'!T8"
    .Range("D1:O21") = .Range("D1:O21").Value
End With

With Workbooks("Daily Calc -- Sw and Expenses.xlsm").Sheets("Calc")
.Range("Q2").Formula = "=iferror(VLOOKUP(Q24,'\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Distrib!$C$9:$W$50000,23,FALSE),0)"
.Range("Q27").Formula = "=IF(SUMPRODUCT(('\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Distrib!$G$8:$G$500000=""B"")*(MID('\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Distrib!$C$8:$C$500000,7,2)=""VR"")*('\\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\[Review.xlsb]Distrib!$H$8:$H$500000<>""B"")),""Yes"",""No"")"
End With

'protect and add filtering ability
With Sheets("Calc")
    .Protect
End With
End Sub

 

[ranman256]

Put the UNC path in a cell or hidden sheet you can edit

public gvNetDir

gvNetDir = range("AB1").value       'aka:  \\East.ad.XXXXX.com\amer\XX\share\Client\UT\XXXX\

i keep other folder paths nearby like in AB2, AB3 , etc, so i can swap out.

 

[ItalianPlatinum]

that crossed my mind just I have many users using this file, not super comfortable with that approach. i have the vba PW protected.

 

[ItalianPlatinum]

Anyone with suggestions how to include it via the VBA?

 

[ItalianPlatinum]

i actually found a way to self-service see below if anyone ever wanted to know how

 

[ItalianPlatinum]

with one typo FileDir = FilePath & FileName