Sleeper: Update macro

[Rakki]

I have a maco that updates links from about 15 other workbooks, these workbooks are named after the week number and so are only created on a weekly basis hence the need to create the update formula in a macro. The code I have works fine but.
What I want to know now is can I change the formula so that it looks for data for one supplier for the set number of weeks and then changes to look for another supplier and so. at the minute I have this code attached to each separate suppliers worksheet.
This is the code I have

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Range("C8").Select
Dim i As Integer
Dim WeekNum As Integer
Dim p As Integer
WeekNum = Range("A2")
For i = 1 To WeekNum
ActiveCell.Formula = "='M:\Liza\Independents\first supplier\[" & i & ".xls]RadioRent'!$F$30"
ActiveCell.Offset(1, 0).Select
Next i
Range("G8").Select
For p = 1 To WeekNum
ActiveCell.Formula = "='M:\Liza\Independents\first supplier\[" & p & ".xls]Credits'!$G$215"
ActiveCell.Offset(1, 0).Select
Next p
Application.ScreenUpdating = True
End Sub

The supplier name can be found in cell B3 of their worksheet.

[mdmackillop]

Hi Rakki,

Welcome to VBAX.

I'm not sure if I have all your requirements right, but have a look at the attached. Save the folders onto your C drive and open the file C:\Atry\Summary.xls. The code there should pick up the relevant data from the other folders and workbooks into the relevant sheets. If your sheets are not named the same as the folders, a revision to the Listing Array could incorporate different folder and sheet names as required.
Regards
MD

[Rakki]

Thanks MD
You have the idea of what I want correct, but using your solition gives me an update dialog box so I still have to manualy point it to the worksheets containing the information, as I will have 52 sheets in 15 folders this just takes to long

[mdmackillop]

Hi Rakki,
I don't get any dialogs. I did once when debugging as I had a file name wrong: 1.xls instead of 01.xls, hence my formatting of i. Perhaps the problem lies there.