Change a Macro commands reference to file locations
 

The following Macro works fine as long as I don't change the location of the
template files. when I copy the blank folder to the weekly folder at say,
C:\Documents and Settings\Tom\Desktop\CHARTER\WK 15 or on a different
machine at C:\Documents and Settings\All Users\Documents\CHARTER\WK 15 I lose
the ability in my
macro to open the WK 15 files. It becomes more complicated in that I use
Microsofts Groove Networks to form a file sharing workspace between offices
and my laptop so when ever I need a local office to run the macro I have to
go in and edit each macro for the right location and because I have 14 Macros
for each week it becomes tedious to setup and maintain. The following is
just one of many lines of code that use the file location. I know I could set
up a C:/Data folder structure on every computer but I was rather hoping I
could tell the Macro to be flexible. My initial thought was that if I saved
the Macro in the workbook instead of personal.xls that when I moved the
workbook the Macro was in the Macro would change with the formulas. Any help
would be appreciated, Thanks.
Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) 0 AND val(trim(cell)) <9521 AND val(trim(cell)) 0
Then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next