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

BABU Baru

New Member
Joined
Nov 5, 2021
Messages
13
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: 36

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 5, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Yes, Customer sheets names are corresponding to the name in Column A.
 

BABU Baru

New Member
Joined
Nov 5, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
I gave customer sheets name in numbers like 1, 2, 3......... and so on
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

BABU Baru

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

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 5, 2021
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,319
Messages
5,836,607
Members
430,441
Latest member
SurendraTantia

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
Top