djh0990
01-18-2010, 06:42 AM
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
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