View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
[email protected] azu_daioh@yahoo.com is offline
external usenet poster
 
Posts: 28
Default 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.