select worksheets in a workbook without knowing the sheet name
Posted by Mark on January 10, 2002 2:10 PM
I am creating a macro to compile data from multiple workbooks. I need to be able to open the workbook then activate the first sheet collect data then continue to the next sheet and so on and so on until all of the data from all the workbooks is obtained.
The problem is I don't know how to select a sheet without knowing the sheet name?? Kind of like an activecell.offset
can anyone help
thanks
Mark
Posted by Dromio on January 10, 2002 2:34 PM
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
'DO SOMETHING
Next
Posted by Barrie Davidson on January 10, 2002 2:34 PM
Mark, here's some code you can adapt to your purposes.
Count = ActiveWorkbook.Sheets.Count
For i = 1 To Count
Sheets(i).Range("A1").Value = 10
Next i
This code will change the value of cell A1 in each sheet to 10.
Hope this helps you out.
BarrieBarrie Davidson
