Create excel spreadsheets that update from a master sheet

Report
Posts
1
Registration date
Thursday October 14, 2021
Status
Member
Last seen
October 14, 2021
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Good Morning

I need to create a master list of individuals in a specific program on a master list that covers all counties in Maryland. I want to be able to add information into the master sheet and automatically update on the specific county subsheet, I cannot figure it out

kind regards

wayne

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
484
Hi Wayne,

May the info below guide you towards your solution:

Topic:
How to auto transfer data from Master to Sub sheets?

Question:
I would like to automatically transfer columns A to G to specified worksheets when condition is met. The worksheets names are located in column F and the condition (when I enter ‘Yes’) is located in column G.

Solution:
Open the Microsoft Visual Basic for Applications window by hitting Alt+F11.
Find your sheets in the left column and double click the Master sheet.
Paste the code in the big white field:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("G")) Is Nothing Then Exit Sub 'When there is no change in column G then do nothing.
'The cell value that was changed in column G will now be referred to as Target.

If Target.Value = "Yes" Then 'When condition is met ("Yes" in column G) then
    Range(Cells(Target.Row, "A"), Cells(Target.Row, "G")).Copy _
    Sheets(Target.Offset(0, -1).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    'Copy the row where the change is made from column A till column G.
    'Paste to the sheet mentioned in column F and to the first available row.
End If
End Sub


The Microsoft Visual Basic for Applications window can now be closed.
Your file is now ready to auto transfer data from Master to Sub sheets.



If you can't figure it out, then let us know more details, so we can help you further.

Best regards,
Trowa