Copy rows based on one cell value and link to another cell value and paste to a new sheet
I need to create a report where I get raw data with a list of transactions, I need my macro to send each transaction to the appropriate sheets based on the portfolio name in column C
I managed to do this, but now I need a Nokia transaction that falls under "Cash" from the reference sheet below to insert under the sheet "Nokia - Cash"
Can someone please help me to build the second part of my code that will help move if C = Nokia and J = Semi Paid and then go to Nokia - Cash?
source to share
This is similar to the previous question I answered.
You don't have to worry about creating sheets and naming them, the code handles it. It also skips items that are not found in the reference sheet.
It matches the description item to the item in your reference sheet, and then matches the map name to the name of the corresponding item position to name the corresponding sheet. If this sheet does not exist it creates and passes the row data, otherwise just pass the row data.
Sub MyClients()
Dim lastrow As Long, lastcol As Long, matchrow As Long, i As Long, j As Long
Dim wsname As String
lastrow = Worksheets("Raw").Cells(Worksheets("Raw").Rows.Count, 1).End(xlUp).Row
lastcol = Worksheets("Raw").Cells(1, Worksheets("Raw").Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For i = 2 To lastrow
On Error Resume Next
matchrow = Application.WorksheetFunction.Match(Worksheets("Raw").Cells(i, 10).Value, Worksheets("Reference").Range("A:A"), 0)
If Err.Number = 1004 Then
MsgBox "Couldn't find item: '" & Worksheets("Raw").Cells(i, 10).Value & "' within reference sheet. Skipping row no: " & i
GoTo skip:
End If
wsname = Worksheets("Raw").Cells(i, 3).Value & " - " & Worksheets("Reference").Cells(matchrow, 2).Value
On Error Resume Next
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(1, 0) = Worksheets("Raw").Cells(i, 1).Value
For j = 1 To lastcol - 1
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(0, j) = Worksheets("Raw").Cells(i, j).Value
Next j
If Err.Number = 9 Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = wsname
For j = 1 To lastcol
Worksheets(wsname).Cells(1, j) = Worksheets("Raw").Cells(1, j).Value
Next j
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(1, 0) = Worksheets("Raw").Cells(i, 1).Value
For j = 1 To lastcol - 1
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(0, j) = Worksheets("Raw").Cells(i, j).Value
Next j
End If
skip:
Next i
Worksheets("Raw").Activate
Application.ScreenUpdating = True
End Sub
source to share