Previous Topic Next Topic

Master Sheet to affect multiple sheets

Closed
akiroche - Oct 5, 2017 at 01:20 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Oct 5, 2017 at 09:46 PM
Hello,

I am not sure how to explain this... I have a master sheet that has a list of all the names and grades for each row alphabetically. I have it color coded as well for anything starting with F or D. With this information, I have separate sheets that have the case workers students and counselors with the same information from the master. Lately, I have been copying and pasting each row from the master to these separate sheets. Is there a way so that if I alter any information from the master it will affect the other sheets? The effect would have to be based on their name and corresponding row.
Could I send you the file? Thanks.

-Aki

Related:

1 response

Answer 1 / 1
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Oct 5, 2017 at 09:46 PM
Hello Aki,

Yes, its a good idea to upload a sample file. However, try the following code in the meantime:- 


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


With this code, I'm assuming:-

- The names are in Column A.
- Your data starts in row 2 with headings in row 1.

The code is a Worksheet_Change event and needs to be placed into the worksheet module. To implement the code:-

- Right click on the Master sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

Now, every time that you enter a name in Column A and then click away (or press enter or down arrow), the relevant row of data will be transferred to the corresponding individual sheet.

Do you need the "used" data to be cleared from the Master sheet after each transfer of data is completed?

Should this not quite work as you would like, then upload a sample of your file to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here. Please use dummy data in your sample.

I hope that this helps.

Cheerio,
vcoolio.
0