About Us

CROD

Don,

Thanks again for your assistance!

"Don Guillett" wrote:

try this. UN tested

sub copyem()
With Worksheets("Sheet 1").Range("a1:a200")
Set c = .Find("NJ1S", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'========
lr=sheets("sheet 2").cells(rows.count,"a").end(xlup).row+1
c.EntireRow.Copy sheets("sheet 2").rows(lr)
'=========
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If

End With
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CROD" wrote in message
...
I can not get the following code to find all values "x" within (column A
of
sheet 1); copy each of these EntireRows; find the first available row (or
a
start row) in sheet 2 and paste all rows from sheet 1(all via a command
button):

Sub Rectangle1_Click()

Worksheets("Sheet 1").Select

With Worksheets("Sheet 1").Range("a1:a200")
Set c = .Find("NJ1S", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Copy
Set c = .FindNext(c)
'Worksheets("Sheet 2").Select
'Worksheets("Sheet 2").Range("a8:a8").Select
'Worksheets("Sheet 2").Paste
'Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If

End With
End Sub

So far I can find and copy the rows sequentially, but, can only paste
undesired results. I've tried numerous iterations.....Help Please!!

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Loop thru sheets copy and then paste in other sheet	LuisE	Excel Programming	2	August 7th 08 07:28 PM
Loop Through Sheets, Copy/Paste if Match	ryguy7272	Excel Programming	10	May 14th 08 06:45 PM
Loop to Filter, Name Sheets. If Blank, Exit Loop	ryguy7272	Excel Programming	3	February 5th 08 03:41 PM
How to create copy & paste loop--rows to new sheets	MFR	Excel Programming	0	October 25th 06 07:50 PM
vba programming copy/ paste cell values help.	yaoming	Excel Programming	3	February 13th 04 08:03 PM

Menu

About Us