Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting data to move to new worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting data to move to new worksheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating and selecting Data from Worksheet | Excel Discussion (Misc queries) | |||
Move data from one worksheet to another. | Excel Worksheet Functions | |||
How do I move data from one worksheet to another? | Excel Discussion (Misc queries) | |||
How to move data on worksheet | Excel Discussion (Misc queries) | |||
Tips on selecting SQL data into a worksheet? | Excel Programming |