I'm newbie to VBA. I'm trying to find some data in Sheet1 and copy it to Sheet2. When I select the data from Sheet1, I need to append it to that already in Sheet2.
I can find and paste the data using the following code, but I can't append it. What have I done wrong in my code?
Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim i As Long
Dim NewSh As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
MyArr = Array("37", "283", "300", "112", "1100", "336", "98")
Set NewSh = Sheets("Sheet2")
With Sheets("Sheet1").Range("B5:B500")
Rcount = 0
For i = LBound(MyArr) To UBound(MyArr)
Set Rng = .Find(What:=MyArr(i), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rcount = Rcount + 1
Rng.EntireRow.Copy NewSh.Range("A" & Rcount)
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next i
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
