Copy certain rows from one worksheet to another
I have this code but I couldn't figure out how to move on to the next
blank row in wsNAME instead of copying it over A2:A2.
-----------
Sub CopyRows()
Dim x As Long
Dim lRow As Long
Dim recType As String
Dim newRange As Range
Dim wsName As Worksheet
Dim acName As Worksheet
Set acName = Worksheets("Orig")
x = 2
y = 2
lRow = InputBox("Enter Last Row Number")
For oRow = 2 To lRow
recType = acName.Cells(y, 1)
Select Case recType
Case "Investigation Div"
Set wsName = Worksheets("Investigation Div")
Case "Anonymous Tip"
Set wsName = Worksheets("Anonymous Tip")
Case "DE 2660"
Set wsName = Worksheets("DE 2660")
Case "Pattern Claims"
Set wsName = Worksheets("Pattern Claims")
Case "Staff Referral"
Set wsName = Worksheets("Staff Referral")
Case Else
Set wsName = Worksheets("Blank")
End Select
Set newRange = wsName.Range("A2:A2") 'I'm stuck here
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Copy
newRange.PasteSpecial
Set newRange = newRange.Offset(1, 0)
'I know the last line is useless since next oRow will initialize
newRange back to A2:A2
x = x + 1
y = y + 1
Next oRow
End Sub
---------
Can someone please help me with the above code.
Thank you so much.
|