Copying Sheet to Sheet
 

I want to write a procedure/macro that will decide if n = 1 To 10000
Step 1 Cells(n, "?") .Value on Sheet 1 equal a Cell.Value on Sheet 2:
if TRUE then I want to copy the entire Sheet 1 Row onto consecutive
rows on sheet 2: then I want repeat the procedure for other values on
sheet 1, up to about a total of 100 values. Would anyone like to help?
Sounds challenging to me but might be a piece of cake for you experts.
Any replies, even comments on either my ignorance or nerve for asking
will be appreciated.


--
knowtrump
------------------------------------------------------------------------
knowtrump's Profile: http://www.excelforum.com/member.php...o&userid=19664
View this thread: http://www.excelforum.com/showthread...hreadid=506107

Copying Sheet to Sheet
 

Sub ABC()
dim rng1 as Range, rng2 as Range
Dim cell as Range, rw as Long
With Worksheets("Sheet1")
set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With

With Worksheets("Sheet2")
rw = .Cells(rows.count,1).End(xlup).Row + 1
set rng2 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
for each cell in rng1
res = Application.Match(cell,rng2,0)
if not iserror(res) then
cell.EntireRow.copy Destination:=rng2.Parent.Cells(rw,1)
rw = rw + 1
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"knowtrump" wrote
in message ...

I want to write a procedure/macro that will decide if n = 1 To 10000
Step 1 Cells(n, "?") .Value on Sheet 1 equal a Cell.Value on Sheet 2:
if TRUE then I want to copy the entire Sheet 1 Row onto consecutive
rows on sheet 2: then I want repeat the procedure for other values on
sheet 1, up to about a total of 100 values. Would anyone like to help?
Sounds challenging to me but might be a piece of cake for you experts.
Any replies, even comments on either my ignorance or nerve for asking
will be appreciated.


--
knowtrump
------------------------------------------------------------------------
knowtrump's Profile:
http://www.excelforum.com/member.php...o&userid=19664
View this thread: http://www.excelforum.com/showthread...hreadid=506107

Copying Sheet to Sheet
 

Tom, Thanks for the help. However, I'm still having problems. First
I haven't quite figured what each line of code or code statement i
doing. My bad I'm sure. Second, when I tried to run the procedure
got a Compile error Variable not defined res=
I tried to Dim res as Range and got Object variable or Withbloc
variable not set
I tried to Dim res as Long and got Run-time error '13': Type mismatch
I'm trying to get everything I can out of Sams, "Teach Yoursel
Microsoft Excel 2000 Programming in 24 Hours." It's slow going and m
smarts aren't yet up to the answer you were nice enough to give me.
Could I prevail upon you a little further

--
knowtrum
-----------------------------------------------------------------------
knowtrump's Profile: http://www.excelforum.com/member.php...fo&userid=1966
View this thread: http://www.excelforum.com/showthread.php?threadid=50610

Copying Sheet to Sheet
 

res should be dimmed as Variant.

application.Match will return either a long or an error value, so you need
to be able to accept either - thus a variant.

Sub ABC()
Dim rng1 As Range, rng2 As Range
Dim cell As Range, rw As Long
Dim res As Variant
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

With Worksheets("Sheet2")
rw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng1
res = Application.Match(cell, rng2, 0)
If Not IsError(res) Then
cell.EntireRow.Copy Destination:=rng2.Parent.Cells(rw, 1)
rw = rw + 1
End If
Next
End Sub

--
Regards,
Tom Ogilvy


"knowtrump" wrote
in message ...

Tom, Thanks for the help. However, I'm still having problems. First,
I haven't quite figured what each line of code or code statement is
doing. My bad I'm sure. Second, when I tried to run the procedure I
got a Compile error Variable not defined res=
I tried to Dim res as Range and got Object variable or Withblock
variable not set
I tried to Dim res as Long and got Run-time error '13': Type mismatch
I'm trying to get everything I can out of Sams, "Teach Yourself
Microsoft Excel 2000 Programming in 24 Hours." It's slow going and my
smarts aren't yet up to the answer you were nice enough to give me.
Could I prevail upon you a little further?


--
knowtrump
------------------------------------------------------------------------
knowtrump's Profile:
http://www.excelforum.com/member.php...o&userid=19664
View this thread: http://www.excelforum.com/showthread...hreadid=506107