Loop through Worksheets

samanthareed2012

New Member
Joined
May 2, 2014
Messages
9
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:

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
 
Last edited:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

andrewkard

Active Member
Joined
Apr 6, 2012
Messages
455
Hello, I think you should to replace these rows:

Next mysheet
Next

on

Next i
Next mysheet
 

samanthareed2012

New Member
Joined
May 2, 2014
Messages
9
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,559
Office Version
365
Platform
Windows
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.
Code:
  VarID = Range(variable).Value
 

samanthareed2012

New Member
Joined
May 2, 2014
Messages
9
Sorry, I'm super new to VBA, but how would I go about referencing the worksheet so that it goes through the worksheets?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,044
Messages
5,466,207
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top