Selecting data to move to new worksheet

  • Thread starter Thread starter Malcolm
  • Start date Start date
M

Malcolm

Hi

I have a spreadsheet with 3 columns of data. The third
column (Holdings) has a "Y" to indicate if it is to be
moved and the cell is left blank if it is to stay.

So, from approximately 2200 rows of information, 700 or so
rows will need to be moved to a new worksheet.

Is there a way to have excel select all the rows where the
3 column has a "Y" and copy the rows to a new worksheet?
If it copies it to a new worksheet, I would like the
copied information to be removed from the original sheet
and all the blank lines moved up.

Many thanks

Malcolm Davidson
 
Sub Macro1()
Dim iCt As Integer
Dim iRow1 As Integer
Dim iRow2 As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
iRow1 = 2
iRow2 = 2
'copy from sheet1 to sheet2
Do Until ws1.Cells(iRow1, 1) = ""
If ws1.Cells(iRow1, 3) = "Y" Then
For iCt = 1 To 3
ws2.Cells(iRow2, iCt) = ws1.Cells(iRow1, iCt)
Next iCt
iRow2 = iRow2 + 1
End If
iRow1 = iRow1 + 1
Loop

'delete from sheet1
For iCt = iRow1 To 2 Step -1
If ws1.Cells(iCt, 3) = "Y" Then ws1.Rows(iCt).Delete
Next iCt

End Sub


HTH,
Merjet
 
Back
Top