Changing worksheet issues

[djh0990]

I'm a writing a little script that manipulates excel data into an XML format in Excel2003. I have an excel document with multiple sheets, and I want to perform an operation for each sheet in a structure similar to whats below, but when it switches sheets, it seems like the do while not operation is performed on the first WS. It should be counting the columns in each open worksheet, but it just recounts the columns in the first For as many worksheets as there are. Any help or suggestions would be appreciated.


For i = 1 To Worksheets.Count
colCount = 1
Worksheets(i).Activate
Worksheets(i).Range("A5").Activate

' While ColumnHeaders are not empty
Do While Not IsEmpty(Cells(columnHeaderRow, colCount))

MsgBox ("Hello World - ColCount Count: " & colCount)

colCount = colCount + 1
Loop
MsgBox ("Final ColCount:" & colCount)
Next i

[Bob Phillips]

Try this



For i = 1 To Worksheets.Count
colCount = 1
With Worksheets(i)

' While ColumnHeaders are not empty
Do While Not IsEmpty(.Cells(columnHeaderRow, colCount))

MsgBox ("Hello World - ColCount Count: " & colCount)

colCount = colCount + 1
Loop
MsgBox ("Final ColCount:" & colCount)
End With
Next i