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

BABU Baru

New Member
Joined
Nov 5, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Attachments

  • 20211105_160302 reduced.jpg
    20211105_160302 reduced.jpg
    128.4 KB · Views: 47

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
Yes, Customer sheets names are corresponding to the name in Column A.
 
Upvote 0
I gave customer sheets name in numbers like 1, 2, 3......... and so on
 
Upvote 0
I gave customer sheets name in numbers like 1, 2, 3......... and so on
Try this:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
Try this: I forgot about keeping formula results:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top