Report

Master Sheet to affect multiple sheets

Ask a question akiroche - Last answered on Oct 5, 2017 at 09:46 PM by vcoolio
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

Helpful
+0
plus moins
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.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!