Copy entire row to matching sheet name (Macro)

[ifeelalone]

How can you copy an entire row to its matching sheet name based on cell value?

Main Sheet: criteria is in column B where the value and its entire row shall be copied to its matching sheet name

Here's the sheet names
1.

2.

3.

 

[Nabeel Gondal]

Hi
this could be helpful for you..!!
https://www.extendoffice.com/docume...into-multiple-worksheets-based-on-column.html

Thanks

 

[ifeelalone]

thank you Nabeel Gondal!
seems okay for me but it doesnt copy the rows after the highlighted one.

 

[My Aswer Is This]

Try this:
You must have a sheet name in column "B" of every row you want copied over

Sub Copy_Row_To_Sheet_Cell_Value()
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Sheets("Summary").Activate
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long
Dim ans As String
    For i = 2 To Lastrow
    ans = Cells(i, "B").Value
    Lastrowa = Sheets(ans).Cells(Rows.Count, "B").End(xlUp).Row + 1
    Rows(i).Copy Destination:=Sheets(ans).Rows(Lastrowa)
    Next
Exit Sub
M:
MsgBox "You do not have a sheet named  " & ans
End Sub

 

[My Aswer Is This]

It's always better to have your totals at the top of your sheet and not as you have them. Otherwise your constantly needing to adjust your Totals Row depending on how many rows you have each month.

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming ..
- the numerical values in those Total rows are the result of formulas
- the individual 'Name' sheets exist and can have any existing data on them removed

.. then you could also try this approach which is to cycle through the 3 (or however many) 'Name' sheets and grab all the relevant rows for that sheet at once, rather than cycling through each row on the Summary sheet and moving them individually to the appropriate sheet.

Test in a copy of your workbook.

Sub MoveToSheets()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    If ws.Name <> "Summary" Then
      ws.UsedRange.Clear
      Sheets("Summary").UsedRange.Copy Destination:=ws.Range("A1")
      With ws.UsedRange
        .AutoFilter Field:=2, Criteria1:="<>" & ws.Name, Operator:=xlAnd, Criteria2:="<>"
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End If
  Next ws
End Sub

For the future, I'd also suggest that you look at the link in my signature block below for providing sample data. You will get many more potential helpers if they can copy the sample data to their worksheets rather than having to type it out from an image.

 

[ifeelalone]

Hi Peter_SSs thank you!

That's what I'm looking for. If you look on the sheet names, it was subtotal-ed per month. Do you have work around with this?

 

[Peter_SSs]

Hi Peter_SSsDo you have work around with this?

I'm not sure what you are asking.

I don't have your actual sheet, but my test sheets also has totals at the end of each month and those total rows are copied to each of the individual sheets and the total row formulas automatically adjust to the reduced rows in each month.

Are you saying that yours doesn't do that?

Can you describe exactly how the subtotals were put on the Summary sheet and tell us what the formulas are in cells D10, E10 and F10 of that first image?

To confirm (I didn't bother entereing the fruit column) here is my Summary sheet

Excel 2010 32 bit
	A	B	C	D	E	F
1	Date	Name	Fruit	qty	price	Total amount
2	12-Jan	Leni		5	6	30
3	13-Jan	Doi		1	6	6
4	13-Jan	Leni		6	3	18
5	13-Jan	Arvin		2	3	6
6	14-Jan	Doi		1	3	3
7	15-Jan	Arvin		1	4	4
8	15-Jan	Leni		8	4	32
9	15-Jan	Doi		2	4	8
10	January Total			26	33	107
11
12	1-Feb	Doi		1	4	4
13	1-Feb	Arvin		1	4	4
14	12-Feb	Arvin		1	4	4
15	12-Feb	Leni		5	6	30
16	14-Feb	Leni		8	3	24
17	February Total			16	21	66
Summary

and here are my other sheets after running the code.
Can you explain which aspects of these result sheets are incorrect? They seem to me to match your images pretty well.

Excel 2010 32 bit
	A	B	C	D	E	F
1	Date	Name	Fruit	qty	price	Total amount
2	12-Jan	Leni		5	6	30
3	13-Jan	Leni		6	3	18
4	15-Jan	Leni		8	4	32
5	January Total			19	13	80
6
7	12-Feb	Leni		5	6	30
8	14-Feb	Leni		8	3	24
9	February Total			13	9	54
Leni

Excel 2010 32 bit
	A	B	C	D	E	F
1	Date	Name	Fruit	qty	price	Total amount
2	13-Jan	Arvin		2	3	6
3	15-Jan	Arvin		1	4	4
4	January Total			3	7	10
5
6	1-Feb	Arvin		1	4	4
7	12-Feb	Arvin		1	4	4
8	February Total			2	8	8
Arvin

Excel 2010 32 bit
	A	B	C	D	E	F
1	Date	Name	Fruit	qty	price	Total amount
2	13-Jan	Doi		1	6	6
3	14-Jan	Doi		1	3	3
4	15-Jan	Doi		2	4	8
5	January Total			4	13	17
6
7	1-Feb	Doi		1	4	4
8	February Total			1	4	4
Doi

 

[ifeelalone]

Great help as always. Thank you Peter_SSs!

 

[ifeelalone]

Hey guys,

I'm doing another file with the same goal - Copy entire row to matching sheet name,
but now, the column C is now my reference and matching sheet name. and I'm given an error with this.

Kindly take note that the column C is a dropdown list (Data Validation-List) type and the whole table was formatted as table.

This is the code that I am using, can you tell me what is wrong?
I am using the code of Mr. Peter.

Sub MoveToSheets()

For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.UsedRange.Clear
Sheets("Summary").UsedRange.Copy Destination:=ws.Range("A1")
With ws.UsedRange
.AutoFilter Field:=3, Criteria1:="<>" & ws.Name, Operator:=xlAnd, Criteria2:="<>"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End If
Next ws
End Sub

For your reference, this is the file I am currently working on. _{https://1drv.ms/x/s!AskvyCa9HdEygxYb0BBo8E2JDjCd}
Thank you for your help.