Copy data from master sheet to multiple sheets based on sheet name of individuals

[BABU Baru]

I want to enter data in the sheet named “Master Sheet” everyday for all fifty customers.

In Row 1 of “Master Sheet”, I have header in Column A, B, C and D, named SHEET NAME, DEPOSIT, WITHDRAWAL and BALANCE respectively.

In Row 2 of Master sheet, I have Sum of cells (B2-C2) in Cell D2 under BALANCE header in Column D

In Row 3 of Master Sheet, I have formula SUM of (D2+B3-C2) under BALANCE header in Column D to calculate everyday balance.

I want to copy data to individual sheet of customers based on sheet name to see every day balance of each customer.

And Data should be paste to next empty row in individual sheet.

Can you please help to write the VBA please?

 

[My Aswer Is This]

So are you saying you want to copy all rows in sheet named Master.
To the sheet name found in column A of sheet named Master.
So if in Column A starting in row 2 of sheet named master we have "Alpha" copy this row to sheet named "Alpha" and paste values.
So if Range("A2") of sheet named Master has "Alpha" copy this row to sheet named "Alpha"
So if Range("A3") of sheet named Master has "Bravo" copy this row to sheet named "Bravo"
And on and on. And all these sheets have already been created.

Is this correct?

 

[BABU Baru]

Yes, Customer sheets names are corresponding to the name in Column A.

 

[BABU Baru]

I gave customer sheets name in numbers like 1, 2, 3......... and so on

 

[My Aswer Is This]

I gave customer sheets name in numbers like 1, 2, 3......... and so on

Try this:

Sub Copy_Rows_To_Sheet()
'Modified  11/6/2021  1:10:22 AM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Dim ans As String
With Sheets("Master")
    For i = 2 To Lastrow
    ans = .Cells(i, 1).Value
    Lastrowa = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
    .Rows(i).Copy Sheets(Cells(i, 1).Value).Rows(Lastrowa)
Next
End With
Application.ScreenUpdating = True
Exit Sub

M:
MsgBox "We do not have a sheet by that name" & vbNewLine & Cells(i, 1).Value
End Sub

 

[BABU Baru]

Thanks for your suggested code but it didn't work.
In Master sheet, I enter data of all customers everyday and getting balance of deposits and withdrawal of all customers in Master sheet

sub sheets already created assigned to each customer.
I want to have balance of each customer in sub sheet.

I also want to update each sub sheet when I enter data in master sheet.

 

[My Aswer Is This]

Try this: I forgot about keeping formula results:

Sub Copy_Rows_To_Sheet()
'Modified  11/6/2021  2:25:25 AM  EST
Application.ScreenUpdating = False
: On Error GoTo M
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Dim ans As String
With Sheets("Master")
    For i = 2 To Lastrow
        ans = .Cells(i, 1).Value
        Lastrowa = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
        .Rows(i).Copy: Sheets(ans).Rows(Lastrowa).PasteSpecial xlPasteValues, Transpose:=False
    Next
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Exit Sub

M:
MsgBox "We do not have a sheet by that name" & vbNewLine & Cells(i, 1).Value
End Sub

 

[My Aswer Is This]

You never said this in original post:
I also want to update each sub sheet when I enter data in master sheet.

Every time you enter any data in Master sheet and have it update in all sheets is:
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[My Aswer Is This]

So if you enter "Alpha" in Range("G1") of sheet named "Master"
You expect "Alpha" to be entered in all sheets in Range("G1")
Is that what your saying

 

[BABU Baru]

Thanks for helping me.
it does not perform sum calculations in sub sheet after data get copied. I think it is because cells reference get changed in sub sheets.
it also overwrites the data when I run VBA second time.