macro for workbook source change

[jw01]

hello

just wondering if anyone can help me out here; i have a worksheet that contains 1000+ entries that uses 52 different workbooks. i maintain a master file that combines data for different weeks for sales employees.

now if i kept the data on my server, it would take me ages to do simple functions like find/replace etc so i kept the file on my c:drive

-so can anyone help me with a macro that can change the workbook path or "change source" through a macro?

the file i work under goes by week for P1W1, P1W2,...P12W5 (P is month and W is week)

and i have the files that the branches sent to me saved on my jrive under a folder called P1W1 etc
-also, each range in my master sheet references the P1W1, so would it possible to utlize that a macro that can change source? i really apprreciate this, im not savy to vba so any help would be great, pls and thxs

[Benzadeus]

You could try replacing all occurences of references.
[VBA]Sub ChangeWorkbookReferences()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
ws.Cells.Replace What:="'C:\Folder\[Workbook.xlsx]Sheet'!" _
, Replacement:="'C:\OtherFolder\[OtherWorkbook.xlsx]SheetName'!" _
, LookAt:=xlPart
Next ws
End Sub[/VBA]