Rakki
09-23-2005, 06:20 PM
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.
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.