Need help to Copy the entire row to another sheet

[spycein]

Hello Everyone,
Need your help to copy data from one sheet to another based on cell value.
For example, I have the following source data set and I am looking for a VBA solution which would copy the row value to a separate sheet as mentioned under the Target Sheet Name example.

i=

Source Sheet Name = Master Sheet
Transaction Type	Reference Number	Dr Account No	Payment Narration	Beneficiary Name	Sheet Name
Cheque	JAN123	123​	Rental	ABC	January
Cheque	FEB123	456​	Stationery	XYZ	February
NEFT	MAR123	789​	Food	XYZ	March
NEFT	MAR456	587​	Travel	ABC	March
Target Sheet Name = January
Transaction Type	Reference Number	Dr Account No	Payment Narration	Beneficiary Name
Cheque	JAN123	123​	Rental	ABC
Target Sheet Name = February
Transaction Type	Reference Number	Dr Account No	Payment Narration	Beneficiary Name
Cheque	FEB123	456​	Stationery	XYZ
Target Sheet Name = March
Transaction Type	Reference Number	Dr Account No	Payment Narration	Beneficiary Name
NEFT	MAR123	789​	Food	XYZ
NEFT	MAR456	587​	Travel	ABC

 

[Marius44]

Hello
With VBA?
A cycle that sweeps all the Sheets, excluding the first one, and, if the first three letters of "Reference Number" are equal to the first three letters of the Sheet name, then find the first empty cell and paste the data you copied.

Hello,
Mario

 

[Anthony47]

I have the following source data set and I am looking for a VBA solution which would copy the row value to a separate sheet as mentioned under the Target Sheet Name example

Assuming that the sheets January, February, etc etc already exist, try this macro:

Sub SpreadToMonth()
Dim I As Long, cMon As String
Dim MCol As String, EMess
Dim CopyCol As String, cCol
'
Sheets("MasterSheet").Select        '<<< The correct name for the starting sheet
MCol = "F"                          '<<< The column with the "month"
CopyCol = "A:E"                     '<<< The columns to be copied
'
For I = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
        cMon = Sheets(Cells(I, MCol).Value).Name
    On Error GoTo 0
    If cMon = Cells(I, MCol).Value Then
        Range(Cells(I, Split(CopyCol, ":", , vbTextCompare)(0)), Cells(I, Split(CopyCol, ":", , vbTextCompare)(1))).Copy _
        Destination:=Sheets(cMon).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Else
        EMess = EMess & ", " & Cells(I, MCol).Value
    End If
Next I
If Len(EMess) > 3 Then
    MsgBox ("Unallocated:" & vbCrLf & Mid(EMess, 3))
Else
    MsgBox ("Completed")
End If
End Sub

Check that the lines marked <<< contains the right parametres

A messagebox will report the completion of the allocation, with the list of any "month" that was not found

 

[spycein]

Assuming that the sheets January, February, etc etc already exist, try this macro:

Sub SpreadToMonth()
Dim I As Long, cMon As String
Dim MCol As String, EMess
Dim CopyCol As String, cCol
'
Sheets("MasterSheet").Select        '<<< The correct name for the starting sheet
MCol = "F"                          '<<< The column with the "month"
CopyCol = "A:E"                     '<<< The columns to be copied
'
For I = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
        cMon = Sheets(Cells(I, MCol).Value).Name
    On Error GoTo 0
    If cMon = Cells(I, MCol).Value Then
        Range(Cells(I, Split(CopyCol, ":", , vbTextCompare)(0)), Cells(I, Split(CopyCol, ":", , vbTextCompare)(1))).Copy _
        Destination:=Sheets(cMon).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Else
        EMess = EMess & ", " & Cells(I, MCol).Value
    End If
Next I
If Len(EMess) > 3 Then
    MsgBox ("Unallocated:" & vbCrLf & Mid(EMess, 3))
Else
    MsgBox ("Completed")
End If
End Sub

Check that the lines marked <<< contains the right parametres

A messagebox will report the completion of the allocation, with the list of any "month" that was not found

Thank you so much @Anthony47 !!

 

[Anthony47]

Thank you for the feedback