Excel Macro Query

[jojowhite]

you "hardcode" the path (this code will work if the file is on local drive):

Sub Seperate_Sheets104()

' jojowhite
Dim RootPath as string


Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Path4 As String
Dim Path5 As String
Dim Path6 As String
Dim Path7 As String


Application.ScreenUpdating = False


'jojowhite
RootPath = Environ$("UserProfile") & "\Desktop\8500999999 - 10 Downing St London\8. Departmental\Projects SPN"


Path1 = RootPath & "\5. Construction\Supplier and Subcontractor\" & "RFQ LOCKED_Yr1Rates" & ".xlsx"
Path2 = RootPath & "\4. Legal\" & "P&C Request"
Path3 = RootPath & "\5. Construction\Metering\" & "Mpan Request Form"
Path4 = RootPath & "\5. Construction\Metering\" & "Contractor Work Instruction"
Path5 = RootPath & "\5. Construction\H&S\" & "Hazard Form"
Path6 = RootPath & "\5. Construction\" & "Indicative Delivery Timeline"
Path7 = RootPath & "\5. Construction\" & "Projects Man Hours Calculator"
...
..

[Kev86]

Welshgasman, unfortunately they have implemented a BOT to do some of the work relating to creating the folder and they now create one folder for all, hence why they now have the departmental folder with unique documents for the different areas we cover. I think it's just starting to look easier to just manually move the spreadsheet out of the Projects SPN folder and move it back to the top of the folder each time.

[Kev86]

Jojowhite, the folders are on a network drive

[Kev86]

Jojowhite, thank you for this but i don't think that will work as the Rootpath will be ever changing as the folder name starting 85* will change based on the job number and also the folder location will move about as it works it way through the team/ business.