Excel: Copying conditional data to new sheet

Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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