Copying Master Sheet Rows to Sheets Based on Column - Assistance

[LDLogDog]

Hi all,

I am working on a Master Contact List in Excel and working to copy an entire row based on a column cell value - emphasis on copy - not deleting the row after entering - simply running an "Update Sheet" macro upon clicking on an "Update" cell or running the macro manually.

Currently I am running through VBA and this is what I currently have, missing the "End If" piece as this is a consistently ongoing list and would be repeated for different types of contacts. What am I missing for this to properly work?

I appreciate any insight - thank you.

Sub MoveRowsToActiveBrokers()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

Set sourceSheet = ThisWorkbook.Worksheets("Master Contact List")
Set targetSheet = ThisWorkbook.Worksheets("Active Brokers")
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
If sourceSheet.Cells(i, "B").Value = "Broker" Then
sourceSheet.Rows(i).Copy Destination: = targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Offset(l)
End If

End Sub

[#2 · Accepted solution]

@LDLogDog 

Does the code in the attached file return the intended result?

Sub MoveRowsToActiveBrokers()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow, lastRow2 As Long
Dim i As Long

Set sourceSheet = ThisWorkbook.Worksheets("Master Contact List")
Set targetSheet = ThisWorkbook.Worksheets("Active Brokers")
lastRow = sourceSheet.Range("B" & Rows.Count).End(xlUp).Row
For i = 3 To lastRow
If sourceSheet.Cells(i, 2).Value = "Broker" Then
lastRow2 = targetSheet.Range("B" & Rows.Count).End(xlUp).Row
sourceSheet.Rows(i).Copy Destination:=targetSheet.Rows(lastRow2 + 1)
End If
Next

End Sub

[#3]

It does - thank you - but one quick question - I have a line of descriptions on Row 2 and when I ran the example, it ended up deleting it and copied the first row about three times. Any idea why that would be?

[#4]

@LDLogDog 

I've added a line of descriptions in row 2 of the "Active Brokers" sheet and the code returns the intended result. Does it work for you in the attached file as well? Otherwise can you attach a sample file without sensitive data?

[#5]

@OliverScheurich 

For some reason it is not letting me upload a similar XLSM file so I had to provide a picture of how the rows are formatted, and the macro is exactly as below. If you know how to upload it let me know and I can upload the file for you to take a look. Thank you!

[#6]

@LDLogDog 

You can send a file by private message. If you select my user icon you can choose messages.

Then you can write a New Message.

Within the new message you can browse, select and attach a file.