Excel: Copying conditional data to new sheet

Closed
Laura - Jan 6, 2011 at 10:07 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jan 7, 2011 at 08:54 AM
Hello,

I have been given a half-done spreadsheet to make into something useable, hopefully someone will be able to help out.

I have a workbook with two sheets - 'Data' and 'Merge Data'

The information in 'Data' is manually input but does have some dynamic data (changing dates to week numbers - but this all works fine). What I need is a something which will copy the complete row from 'Data' to 'Merge Data' if any of the cells from 'Data' L:N are equal to cell B1 in 'Data'.

In Visual Basic there is the following:

Sub test()
Set a = Sheets("Data")
Set b = Sheets("Merge Data")
Dim x
Dim z

x = 1
z = 2

Do Until z = 9999

If a.Range("L:N" & z) = "a.Cell(B1)" Then
x = x + 1
'b.Rows(x).Value = a.Rows(z).Value
a.Select
Rows(z).Copy

b.Select
Rows(x).Paste.Paste Special

End Sub

It doesn't work.

Help?!

Thanks

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 6, 2011 at 10:24 AM
Hi Laura,

I don't understand how a range of cells can ever be the same as the value of a single cell.

I could write a code for you which checks every cells in range L1:N9999 and when a cells matches the value of cell B1, copy it's entire row to the next sheet.
But keep in mind that if cell L1, M1 and N1 are all the same value as B1, the same row will be copied three times to the next sheet.
Also keep in mind that checking 30000 cells takes excel some time to "think".

Give me your thoughts on this.

Best regards,
Trowa
0
Hi

Cells L1, M1 and N1 will never be the same as they are populated by differing formulae (K1+2 / K1+4 / K1+6). It's only when one of them matches that the row should be copied.

Also, it would be at maximum 150 or so rows of data - less thinking time!

Laura
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jan 7, 2011 at 08:54 AM
Hi Laura,

OK, then try this code:

Sub test() 
Set MR = Sheets("Data").Range("L1: N150") 
    For Each cell In MR 
If cell.Value = Sheets("Data").Range("B1").Value Then 
cell.EntireRow.Copy 
Sheets("Merge Data").Cells(Rows.Count, "L").End(xlUp).Offset(1, -11).PasteSpecial 
    End If 
        Next
Application.CutCopyMode = False
End Sub

Does this code represent what you want to execute?

Best regards,
Trowa
0