finding and copy info (xp)

[cslauenwhite]

I am having a problem.
I have attached a file to help me.
I want to go down column R and every time I find a value above "0" I want to copy certian things off that row onto a different sheet.

[LegareColeman]

Which "certain things" do you want to copy, what sheet do you want to copy them to, and where on that sheet?

[cslauenwhite]

Rows E, G, and K:O to a second page (which is allready created called collected data) to the following cells in row 5 (after the first row of info is inputed there is a row insereted is that the most current info is on top; that way all the info copied is into the same row) E will go into A G into B K:O into C:G

[HansV]

Try this macro. You can adapt it if necessary.

Sub CopyInfo()
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim lngSourceRow As Long
Dim lngMaxRow As Long
Dim lngTargetRow

Set wshSource = Worksheets("Sheet1 (2)")
Set wshTarget = Worksheets.Add

lngMaxRow = wshSource.Range("R65536").End(xlUp).Row
For lngSourceRow = 1 To lngMaxRow
If wshSource.Range("R" & lngSourceRow) > 0 Then
lngTargetRow = lngTargetRow + 1
wshSource.Range("E" & lngSourceRow).Copy _
Destination:=wshTarget.Range("A" & lngTargetRow)
wshSource.Range("G" & lngSourceRow).Copy _
Destination:=wshTarget.Range("B" & lngTargetRow)
wshSource.Range("K" & lngSourceRow & ":O" & lngSourceRow).Copy _
Destination:=wshTarget.Range("C" & lngTargetRow)
End If
Next lngSourceRow

Set wshTarget = Nothing
Set wshSource = Nothing
End Sub

[HansV]

Oops - you changed the requirements at the same moment I posted my macro. Here is a new one.

Sub CopyInfo()
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim lngSourceRow As Long
Dim lngMaxRow As Long
Const lngTargetRow = 5

Set wshSource = Worksheets("Sheet1 (2)")
Set wshTarget = Worksheets("Collected Data")

lngMaxRow = wshSource.Range("R65536").End(xlUp).Row
For lngSourceRow = 1 To lngMaxRow
If wshSource.Range("R" & lngSourceRow) > 0 Then
wshTarget.Rows(lngTargetRow).Insert
wshSource.Range("E" & lngSourceRow).Copy _
Destination:=wshTarget.Range("A" & lngTargetRow)
wshSource.Range("G" & lngSourceRow).Copy _
Destination:=wshTarget.Range("B" & lngTargetRow)
wshSource.Range("K" & lngSourceRow & ":O" & lngSourceRow).Copy _
Destination:=wshTarget.Range("C" & lngTargetRow)
End If
Next lngSourceRow

Set wshTarget = Nothing
Set wshSource = Nothing
End Sub

[cslauenwhite]

What does this line do:
lngMaxRow = wshSource.Range("R65536").End(xlUp).Row
I am assuming that it is assiging 1 up from the last row to IngMaxRow
Could you change ("R65536") to ("R200") since this is the last possable row for information?
sorry for being a pain but I learn better if I can rip a piece of code apart and make it do something else, then I inderstand it better.
thanks

[HansV]

Range("R65536").End(xlUp) is the last filled (non-blank cell) in column R. It is the cell that would be selected if you clicked in R65536 (row 65536 is the bottom row of the worksheet), then pressed End followed by the Up arrow. So Range("R65536").End(xlUp).Row is the row number of the last filled cell in column R. This technique is very useful when you're dealing with a variable amount of data, and you don't know in advance how many rows will be populated. End(xlUp) will always find the last filled cell, whether it's in row 4, row 1000 or row 23000.

(Similarly, you can use Range("IV4").End(xlToLeft) to find the last filled cell in row 4.)