Forum Discussion
Copying Master Sheet Rows to Sheets Based on Column - Assistance
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 SubDoes 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
5 Replies
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- 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?
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?
