View Full Version : Solved: Row Count / Loop
Rlb53
03-16-2012, 05:20 PM
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
03-16-2012, 05:35 PM
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
03-16-2012, 05:56 PM
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
03-17-2012, 02:53 AM
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
03-17-2012, 01:40 PM
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
03-17-2012, 02:53 PM
It looks indented to me, the VBA tags do that.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.