Loop through Worksheets

[samanthareed2012]

Hello!
I want my code to loop through the worksheets so I used For each-Next,. but it brings up an error "invalid next control variable reference".
I have more to my code but for space and time saving I have removed the more lengthy parts. Any help would be much appreciated!

Code:

Dim mysheet As Worksheet

For Each mysheet In ActiveWorkbook.Worksheets

'Counter for Excel worksheet
    For i = 1 To numCells
    variable = "A" & CStr(i)
    Range(variable).Select
    VarID = Range(variable).Value
    VarDes = "C" & CStr(i)
    Des = Range(VarDes).Value
    Dec = "U" & CStr(i)
    DecVal = Range(Dec).Value
    ResponseType = "N" & CStr(i)
    datatypevalue = Range(ResponseType).Value
    DataType = "T" & CStr(i)
    datatypevalue = Range(DataType).Value
    Section = "F" & CStr(i)
    ResponseOptions = "P" & CStr(i)
    resopvalue = Range(ResponseOptions).Value
'Text Areas
        If InStr(Range(ResponseType).Value, "textarea") Then
        wordTable.Rows.Add
        wordCount = wordCount + 1
        wordTable.Cell(wordCount, 1).Range.Text = wordCount - 1
        wordTable.Cell(wordCount, 2).Range.Text = VarID
        wordTable.Cell(wordCount, 3).Range.Text = Des
        wordTable.Cell(wordCount, 4).Range.Text = "Leave text field blank"
        wordTable.Cell(wordCount, 5).Range.Text = "Accepts input"
        wordTable.Rows.Add
        wordCount = wordCount + 1
        wordTable.Cell(wordCount, 1).Range.Text = wordCount - 1
        wordTable.Cell(wordCount, 2).Range.Text = VarID
        wordTable.Cell(wordCount, 3).Range.Text = Des
        wordTable.Cell(wordCount, 4).Range.Text = "Enter 3999 numeric, character, and special character values"
        wordTable.Cell(wordCount, 5).Range.Text = "Input Accepted"
        wordTable.Rows.Add
        wordCount = wordCount + 1
        wordTable.Cell(wordCount, 1).Range.Text = wordCount - 1
        wordTable.Cell(wordCount, 2).Range.Text = VarID
        wordTable.Cell(wordCount, 3).Range.Text = Des
        wordTable.Cell(wordCount, 4).Range.Text = "Enter more than 3999 characters"
        wordTable.Cell(wordCount, 5).Range.Text = "Input not accepted"

End If
        
Next mysheet
    
        Next
wordTable.Rows(1).HeadingFormat = True

WDApp.ActiveDocument.Save

End Sub

 

[andrewkard]

Hello, I think you should to replace these rows:

Next mysheet
Next

on

Next i
Next mysheet

 

[samanthareed2012]

Thank you, that helped - I no longer get an error message, but it only loops through the same worksheet.

How do I fix that?

 

[Norie]

Your code loops through the same worksheet because you don't reference the loop variable mysheet anywhere within the code.

Without a worksheet reference something like this will refer to a range on the active worksheet.

  VarID = Range(variable).Value

 

[samanthareed2012]

Sorry, I'm super new to VBA, but how would I go about referencing the worksheet so that it goes through the worksheets?