create excel spreadsheets that update from a master sheetClosed

Question

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

TrowaD · Answer

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

Create excel spreadsheets that update from a master sheet

1 response

Similar discussions

Subscribe To Our Newsletter!