bringing certain data from sheet2:sheet9 to sheet1

[G. Beard]

I have a macro that sorts data on sheet2:sheet9. The purpose of this was to
move all rows with completed data (data not to be moved to sheet1) to the
bottom of the list of data. The completed data is determined by columnH
who's header is "% complete". If this =100 then I don't use it. On sheet1
I have a cell that counts the number of rows that have data that is less
than 100% complete on each sheet from sheet2:sheet9 (basically from m1:n9 I
have the sheet name down m1:m9 and the number of rows I want to copy to
sheet1 from n1:n9).

M N
1 sheet2 30
2 sheet3 10
3 sheet4 60
4 sheet5 105

Can I write a loop in vba that will copy the first 30 rows from sheet2 and
the first 10 rows from sheet3 and the first 60 rows from sheet4 and so on to
sheet1? The data I want copied is in columnA:columnI on each sheet. So, on
sheet1 I want to fill A1:I205 with the data from sheet2:sheet from my
example above.
Is there a better way of doing this? Am I on the right track? Am I at
least on a possible track?

Any help is appreciated,
Gary

 

[Patrick Molloy]

try this

Option Explicit
Sub CopyData()
Dim wsTarget As Worksheet
Dim TargetRowindex As Long
Dim SourceRowindex As Long
Dim sheetindex As Long
Dim wsSource As Worksheet
Dim SourceData As Range
Set wsTarget = Worksheets("Sheet1")
wsTarget.Cells.ClearContents
wsTarget.Range("K1") = sum
For sheetindex = 2 To 9
Set wsSource = Worksheets("Sheet" & sheetindex)
SourceRowindex = 1
With wsSource
Do Until .Cells(SourceRowindex, "H") = ""
If .Cells(SourceRowindex, "H") < 100 Then
Set SourceData = .Range(.Cells(SourceRowindex, "A"),
..Cells(SourceRowindex, "I"))
TargetRowindex = TargetRowindex + 1
wsTarget.Range(wsTarget.Cells(TargetRowindex, "A"), _
wsTarget.Cells(TargetRowindex,
"I")).Value = _
SourceData.Value

End If

SourceRowindex = SourceRowindex + 1
Loop
End With
Next

End Sub

 

[G. Beard]

Patrick,
Thanks for the help. I'm getting a "variable not declared" error at:

wsTarget.Range("K1") = Sum

I'm not sure what this line is supposed to do, so if you could lend some
insight...I'd appreciate it.

Thanks again for the help,
Gary

 

[G. Beard]

Patrick,
When I take out the line:

Option Explicit

I get a "syntax error" at line:

Set SourceData = .Range(.Cells(SourceRowindex, "A"),


Gary

 

[Patrick Molloy]

there is one line - ,aybe your viwer rolled to two lines...

Set SourceData = .Range(.Cells(SourceRowindex, "A"), .Cells(SourceRowindex,
"I"))

 

[Patrick Molloy]

that wa smy test line to prove that the number of rwos in the result
tallied...soory...meant to delete it. you don't need it.

always use OPTION EXPLICIT

it enforces good practice by making you declare your variables. many 'bugs'
are actually simply typing the variable name incorrectly!

 

[Patrick Molloy]

the file is here:
www.xl-expert.com/files/gb_1.xls