Code help needed.

Hi.
Thanks to all who have helped me so far.

This is hopefully the last step!

I have now got a macro that looks at a worksheet in one workbook (source.xls), and uses the data to write a list in a different workbook (dailystaff.xls). What I need is to run the macro in a For/Next loop writing lists on a series of different worksheets (all in dailystaff.xls). The macro currently contains the line:

Set mySht2 = Workbooks("DailyStaff.xls").Worksheets("Day1")

which defines where the list is to be written. DailyStaff.xls actually contains 28 worksheets, and I would like to be able to have the worsheet name change automatically to Day2, Day3 etc. The alternative would be to write out the whole macro 28 times, which would be cumbersome and plain ugly.

For x = 1 to 28
Set mySht x+1 = Workbooks("DailyStaff.xls").Worksheets("Day x")
[rest of macro here]
Next x

This would obviously not work as it is, but what I need is the correct syntax for having a variable as part of a filename.

Thanks
David.

Hello David,

You were so close to solving this yourself. The Workbook and Worksheets are Collection objects. All collections can be referenced one of two ways. You can reference an item by Index it's index value or by it's key. The key is a String.

So your code can written Using Keys:

Dim mySht as Worksheet
For x = 1 to 28
Set mySht = Workbooks("DailyStaff.xls").Worksheets("Day" & Str(X))
[rest of macro here]
Next x

Or This Way with Index Values:

Dim mySht As Worksheet
For x = 1 to 28
Set mySht = Workbooks("DailyStaff.xls").Worksheets(X)
[rest of macro here]
Next x

Sincerely,
Leith Ross