Solved: Row Count / Loop

[Rlb53]

I have a List of Worksheet Names in "Data Sheet 2".
I want to access each of the worksheets in the list and transfer data from specific ranges within each. I will place the information in a form for a report.
I have the code (Loop) below, but I'm failing to acquire the "Next i" and identify it as a "Sheet Name" to "Activate".

Could you kindly assist ?

Thank you in Advance for Reviewing!

Dim i As Integer

Dim intRowCount As Integer

intRowCount = Sheets("data sheet 2").Range("m3").CurrentRegion.Rows.Count - 1
For i = 1 To intRowCount

Sheets("data sheet 2").Range("m3" & i).Activate

Sheets(ActiveCell.Value).Activate

With ActiveSheet
.AutoFilterMode = False
End With

With ActiveSheet.Range("a8", Range("a" & Rows.Count)).End(xlUp)
.AutoFilter 1, plant_no.Value
.Range("b3").Copy
'...... Rest of Code.....
End With

Next i

[Bob Phillips]

Not sure what you mean, but the code can be simplified


Dim i As Long
Dim intRowCount As Long
Dim sh As Worksheet

With Sheets("data sheet 2")

intRowCount = .Range("m3").CurrentRegion.Rows.Count - 1

For i = 1 To intRowCount

Set sh = Worksheets(ActiveCell.Value)

sh.AutoFilterMode = False

sh.Range("a8", sh.Range("a" & sh.Rows.Count)).End (xlUp)
sh.AutoFilter 1, plant_no.Value
sh.Range("b3").Copy
'...... Rest of Code.....
Next i
End With

[Rlb53]

Thanks ! XLD

I'm still working with it... Recieving an error " Invalid Use of Property"

VBA Editor is pointing to ".End"

within "sh.Range("a8", sh.Range("a" & sh.Rows.Count)).End (xlUp)"

[Bob Phillips]

See if this works better


Dim i As Long
Dim intRowCount As Long
Dim sh As Worksheet

With ActiveSheet 'Sheets("data sheet 2")

intRowCount = .Range("m3").CurrentRegion.Rows.Count - 1

For i = 1 To intRowCount

Set sh = Worksheets(ActiveCell.Value)

sh.AutoFilterMode = False

With sh.Range("a8", sh.Range("a" & sh.Rows.Count).End(xlUp))

.AutoFilter 1, plant_no.Value
.Range("b3").Copy
'...... Rest of Code.....
End With
Next i
End With

[Rlb53]

Thank you Again XLD !

I was headed in the wrong direction earlier. I ended up with the following code, which is a loop, but it gives me what I was looking for.

The ability to gather information from specific ranges within worksheets named in a column of "Data Sheet 2" and place it in a separate worksheet to generate a printable form. (the print code is not shown).

The Loop ends when the last cell within the column is empty.

Thank you again for your assistance ! (Quick Question. I copy my code into this response but it doesn't come out indented and spaced as it is on my VBA Editor. How can I do this on the Forum so the code is more easily read?)

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("data sheet 2").Range("m3").Activate

Line1:
Sheets(ActiveCell.Value).Activate
ActiveSheet.AutoFilterMode = False

ActiveSheet.Range("b3").Copy 'ProductID
Sheets("inv report form").Activate
Range("a1000000").End(xlUp).Offset(1, 0) = plant_no.Value
Range("b1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
Sheets("data sheet 2").Activate
Sheets(ActiveCell.Value).Activate

ActiveSheet.Range("b4").Copy 'Description
Sheets("inv report form").Activate
Range("c1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
Sheets("data sheet 2").Activate
Sheets(ActiveCell.Value).Activate

ActiveSheet.Range("c5").Copy 'Unit
Sheets("inv report form").Activate
Range("d1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
Sheets("data sheet 2").Activate
Sheets(ActiveCell.Value).Activate

With ActiveSheet.Range("b8", Range("b" & Rows.Count)).End(xlUp)
.AutoFilter field:=2, Criteria1:=Me.plant_no.Value
ActiveSheet.Range("f2").Copy 'Quantity
' ActiveSheet.AutoFilterMode = False
Sheets("inv report form").Activate
Range("e1000000").End(xlUp).Offset(1, 0).Activate
ActiveCell.PasteSpecial (xlPasteValues)
End With

Sheets("data sheet 2").Activate
ActiveCell.Offset(1, 0).Activate



If ActiveCell.Value = NullString Then
Exit Sub
Else
GoTo Line1:
End If

End Sub

[Bob Phillips]

It looks indented to me, the VBA tags do that.