Iterating all worksheets in Excel

[nebulom]

How can I iterate all the worksheets in Excel with the Macro->Visual Basic? Or does this matter if it's a Macro or not. Not totally familiar with VB6 so bear with me.

[westconn1]

Dim s As Worksheet
For Each s In Sheets
    Debug.Print s.Name  ' put you code in here
Next

[nebulom]

Millions of thanks!

[nebulom]

Btw, I can't iterate the same sheets twice. It goes only for one iteration.

For Each w in Sheets
. . . ' Do something
Next

For Each w in Sheets
. . . ' Do another thing -- This goes only for the last sheet
Nex

Is there any specific functions to reset the sheet?

[ganeshmoorthy]

im not sure, try something like this

For Each w in Sheets
. . . ' Do something
Next
set w=nothing
set w = New Worksheet
For Each w in Sheets
. . . ' Do another thing -- This goes only for the last sheet
Next

[nebulom]

Hehe. For some weird reason, for each is not iterating. Hehe. Anyway, thanks!

[RobDog888]

Post #2's code works just as you wanteed, at least for me. The sheets are all in the same workbook and running the code in Excels VBA IDE?

[nebulom]

Hmmm. Yeah I can see that it can iterate at least when I set a counter for the sheets. But didn't save to file. Here's my code.

Dim rowData As String
    
    Dim fileName As String
    Dim boxConfCode As String
    
    Dim w As Worksheet
    
    ' Save for Box Configuration
    fileName = Application.GetSaveAsFilename(fileFilter:="All Files (*.psv), *.psv")
    If fileName <> "" Then
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set writer = fs.CreateTextFile(fileName, True)
        
        For Each w In Worksheets
            With w
                rowData = ""
                boxConfCode = Trim(.Cells(2, 1).Value)
                rowData = rowData + boxConfCode
                rowData = rowData + "|" + Trim(.Cells(2, 2).Value)
                writer.WriteLine (rowData)
            End With
        Next
        
        writer.Close
    End If
    
    ' Save for Box Configuration Operations
    fileName = Application.GetSaveAsFilename(fileFilter:="All Files (*.psv), *.psv")
    If fileName <> "" Then
        Set fs2 = CreateObject("Scripting.FileSystemObject")
        Set writer = fs.CreateTextFile(fileName, True)
        rowCounter = 6
        
        For Each w In Worksheets
            With w
                While Trim(.Cells(rowCounter, "A").Value) <> ""
                    rowData = ""
                    rowData = rowData + boxConfCode
                    rowData = rowData + "|" + Trim(.Cells(rowCounter, 1).Value) + "|"
   
                    writer.WriteLine (rowData)
                    rowCounter = rowCounter + 1
                Wend
            End With
        Next
   
        writer.Close
       'MsgBox "Save to " + fileName
    End If

This may not be related to sheets iteration because of the fact that IT IS iterating.

[nebulom]

Bahahaha. I admit. I AM stupid. It's the rowCounter. It iterates alright. Thanks mates. Good day.