excel macro to Find & Copy Data

Microsoft Ms sel office xp pro/frontpage...
April 30, 2010 at 10:36:13
Specs: windows xp
I have a macro that searches 4000 rows of data which returns many results. The macro stops when a result is found and I have to copy and paste the criteria and the result to the next blank row in another sheet.
The criteria is in sheet A range G2:K2
The Result is in sheet A range H6:K6 and H10:K10
How do I record these 3 ranges sequentially on a row on sheet B without stopping the run to do it manually?
Please can you help.

See More: excel macro to Find & Copy Data

Report •

April 30, 2010 at 10:40:54
It might help us help you if you would post the code you are currently running.

Since we can't see your code from where we're sitting, it's a bit difficult to suggest modifications.

Report •

May 1, 2010 at 04:27:01
Greetings Derbydad03.
If the criteria is matched the macro goto 200 otherwise goto 100 and do it all again. It is at goto 200 that I need the copy and paste.

If Range("j6") > 2 Then
GoTo 200
End If

If Range("K6") > 0 Then
GoTo 200
End If
' loop again
GoTo 100

Range("G2:K2", "H6:K6", "H10:K10").Copy
Range("b4").End(xlDown).Offset(1, 0) = ActiveCell
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

end sub

Report •

May 1, 2010 at 05:45:27
While I'm not sure I completely understand what you are trying to do, I'll offer this.

As far as I know, you can't copy non-contiguous ranges in either VBA or Excel. They have to be copied as individual ranges.

Since you want all of the ranges on a single Row, I adjusted the Offset arguments to start the Paste of the first range (G2:K2) in Column B, then start the Paste of H6:K6 in Column G (on the same row) and finally start the Paste of H10:K10 in Column K (on the same row).

Is that what you were looking for?

Even if it's not, take a look at the code I offered anyway. You'll notice that I did not Select or Activate any cells or Ranges.

In the vast majority of cases, you do not need to Select a range within VBA to perform an action on it. You can perform the action just by referencing the range

Sheets("RESULT").Range("B4").End(xlDown).Offset(1, 0).PasteSpecial _

Sheets("RESULT").Range("B4").End(xlDown).Offset(0, 5).PasteSpecial _

Sheets("RESULT").Range("B4").End(xlDown).Offset(0, 9).PasteSpecial _

Report •

Related Solutions

May 1, 2010 at 06:16:51
Thank you. The code works perfectly.

Report •

May 1, 2010 at 07:39:21
Glad to have been of assistance.

Report •

Ask Question