Autopopulating multiple sheets from one main sheet [Solved/Closed]
Mikey -
Feb 20, 2013 at 05:39 PM - Latest reply: TrowaD2356
Posts Sunday September 12, 2010Registration dateModeratorStatus
May 24, 2018
Last seen
- Oct 5, 2017 at 11:35 AM
I'm making an accounting workbook for an organization I am the treasurer for, and I've run into a problem.
On the first sheet "Account" I input all of the transactions that the club has, on a table that goes from C6-H6, and on down for every entry. One of the columns in the table is labeled "Sector" and refers to which part of the club the expense or revenue came from. There are 7 sectors of the club and they each have a sheet in this workbook.
What I would like to do, is have each individual sector's sheet automatically populate with data when it's inputted onto the main "Account" sheet, and have it based on the Sector column in the table. So basically, if the sector says "General", I would like for the entire row to be transferred to the sheet "General". And so on for each individual sector.
That way I don't have to copy and paste the hundreds of transactions to individual pages manually.
Any tips or suggestions?
For the code below I created 2 sector's "General" and "1ste Group".
Adjust and add to the code to suit your needs.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub
If Target.Value = "General" Then
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets("General").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
ElseIf Target.Value = "1ste Group" Then
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets("1ste Group").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub
To implement the code:
Right-click on the "Account" tab and select View code.
Paste the code in the big white field.
Tip: Have you considered drop down lists for column H?
That is strange, saving your file shouldn't alter the way the code works in any way.
I wouldn't mind taking a look at your file to see what is going on.
Use a free file sharing site like www.speedyshare.com or ge.tt to upload your file and then post back the download link. Always be careful with sensitive information.
The most efficient way would be to use an extra column to make each row unique. The row can then be found on the sub sheets. So if you want to update your change, you can confirm the entry in column A. This will activate the code which will look for the row using the value from the extra column. If the row is found, then it will be replaced. Otherwise the row will be added.
Note that the extra column can be hidden, placed out of sight (like column ZZ or something) or the text can be made white to not disturb the format of the sheet.
change the "C" into an "A".
And in that case all the code blocks are the same, which means you could consider the code posted in the 14th post in this thread. Which will remove the need for any additional code blocks.
I have a similar issue I'm working on that I could use some help on.
I've got a spreadsheet that I'm using as a punch-list to track open issues on a project. These individual punches are tracked by 3 categories, Mechanical, E&I, HSE, and then are listed as Open or Closed, plus a few others. I'm trying to populate sheets labeled "Mechanical", "E&I", and "HSE" but only with "open" items. I also need it to update every time an item changes from open to close, meaning it's removed from the sheet.
I used the code Suzanne submitted above with the changes you suggested and it's sort of working. I'm occasionally getting a type mismatch error, have no idea how to prevent duplicate entries, no idea how to only pull open items, and then of course remove items that change from open to closed.
1) Which column is used for the labels "Mechanical", "E&I" and "HSE"?
2) Which column is used for 'Open' and 'Closed'?
3) And finally which column shows unique values? This is used to find an entry which has already been copied, to prevent duplicate entries and to remove entries where the status is changed to 'Closed'.
This post really helped me at some extend. Thank you! :)
I'm still getting some bug to implement the same on my requirements. Please help me if possible ..
I'm having a 'Master' sheet. Column A of 'Master' is Type. I have drop down list for Type. Type can be - Email, Page, Abend and Ticket. Now depending on the type entered in Master I want the data to be auto populated at respective child sheet of same name. From Master, column A to column K should be copied to column A to column K at respective sheets.
Using below query I did able to accomplish that. But I'm seeing two bugs -
1) When I select value from drop down at col A (this is the first column in the sheet) in "Master", a row is being populated at respective child sheet. But col B to col K in child sheet is remaining blank even after I enter the values at col B to K . I can't move col A at last as per requirement. I want col B to col K in child sheet also to be populated once I enter value for the same in Master.
2) What if someone just copy and paste the values (from his local copy) in Master shit (in compatible format)? Even though the Type is correct, it is not being populated in respective child sheet.
Please help me to rectify the same. Below is the query I used -
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
If Target.Value = "Page" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Page").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
If Target.Value = "Abend" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Abend").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
If Target.Value = "Email" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Email").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
If Target.Value = "Ticket" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Ticket").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
1) Not sure what goes wrong here since I can't recreate the error. When I fill columns B to K with data and then use a dropdown list in column A to select Page, columns A to K are copied to the first available row on the Page sheet. Just as you want, right?
2) Target is referred to as the range that is changed. So you basically can't change more then one cell or you have to adjust the code to make excel only look at the column A part of the pasted range.
Change "Target.value ="
Into "Cells(Target.Row, "A").Value"
I am trying to do the exact same thing that you helped Michael and Soumyajyoti with. My table extends from column A to AN, and the values that I would like to determine the population of new tabs lie in column C. I copied your code and pasted it into the tab that contain all of the input data like you said in your original comment to Michael, and I assume I adjusted the column and value name references accordingly, but it is not working. My biggest issue is that even when I save the macro, I cannot view it in my macros list when I press ALT+F8. This has been a problem anytime I have tried to use VBA, so any insight is appreciated. I am running Excel 2010 on a PC.
The code provided will run automatically whenever a change is made to column H in the Account sheet.
The code starts with Private Sub Worksheet, thus is placed in a sheet and can not be manually activated. Only codes placed in a Module can be found in the macro list when pressing Alt+F8.
Hopefully this makes some sense.
Let me know if this solves your query or you would like more assistance.
Consider the option to post the code you are trying to get to work.
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Hi TrowaD
I am new to macro programming.
I have a similar requirement and used the same code as suggested by you. I need to populate the multiple sheets from master sheet based on 1st column. Each sheet would get updated as whenever the new entry is done in the master sheet. The header is same for all the sheets.
When I use the below code , it doesnot run
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub
If Target.Value = "General" Then
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets("General").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Private Sub Worksheet_Change(ByVal Target As Range)
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)
End Sub
To implement the code: Right-click on the "mastercopy" tab and select View code.
Paste the code in the big white field.
After doing that, make a change in column A in the "mastercopy" sheet and the entire row will be copied to the first available row of the sheet mentioned in column A. So make sure the sheet name in column A is the last value you enter for that row.
Your code is of great help and it works fine. Now my team has asked for one more functionality to add to the existing code. Once the row in inserted in the sheet, we want that whenever any row is UPDATED in the master sheet, the same row should get updated in the individual sheet as well. Is that possible?
At present a new entry is sent to the individual sheet for every update of the existing rows in the master sheet. Please note that only those rows would be updated which have 'O' under header Open/Executed( Col O)
Can we have something like update/refresh option in the sheet for which the update can be done.
Please suggest a solution to it. Thanks for help in advance. You have been of great h
elp to me.
I am attempting to use the code you supplied. I am trying to set it up to copy what I have entered in an entire row to another tab based on what I type in a specific column.
If I type the number 525 in column C I want whatever row I am on to then copy or be moved to the sheet that is labeled 525.
How must a change the code you gave to make this happen?
When a change is made to a row, the originally matching rows aren't the same anymore. So how do we know which row needs to be replaced?
If your Mastercopy sheet has all the correct and latest data, then we could run a code when selecting one of the sub sheets.
For example: when selecting sheet A, all data on that sheet except for the headers will be deleted. Then all the rows from Mastercopy sheet with an A in column A will be copied to sheet A.
Is that something that could work for you and your team?
Also the Open/Executed header is in column N in the file you send me, did something change?
Hi TrowaD
Thanks for the reply.
I think you have suggested a right approach. We can actually delete all the rows from the sheet A and then copy all the rows to the sheet A from mastersheet. But will it work for all the sheets based on column A? It would be great if you can you help me with the code.
IS there any other approach also?
Just to let you know, open/executed header is 'N'. I had mistakenly written it wrong. Sorry for the inconvenience.
I'm trying to do something similar in a workbook (and eventually a google sheet). I have salesperson data in a mastersheet that then needs to be directed to sub-sheets for each individual salesperson.
If the salesperson listed in the master sheet in column B is "Salesperson 1", I would like the entire row to transfer over to the sub-sheet "Salesperson 1".
Please let me know if you have any questions; I really appreciate the help!
Try the code below. Keep in mind that once implemented (right-click master sheet tab > View code > paste code in big white field) the code will run automatically once an entry is made in column B. So make the salesperson the last entry you make for that row.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
target.entirerow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub
Hello Trowa,
I hope you're still around answering questions! I tried to use your code above, and I have no idea why it's not working... :(
Here is what I typed into the (activated) VB box:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub
If Target.Value = "Amex" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("Amex").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
My sheet called "all" contains:
Column A: Date
B: Pymt (which would be a check#, Amex, Visa, etc)
C: Vendor
D: Notes
E: Job
F: Category (Materials, commissions, etc)
I want a verbatim copy of the sheet "all", columns A-F on the next line on the respective sheets Amex, Visa, (or whatever). I've made a database in the past, but I don't want to spend a year trying to remember to not delete or insert a row.
I have just used this code and found it very helpful - it seems to be working, except that every time I make a change in the Mastersheet- it adds a new entry to the other sheet, rather than replacing it. Did I some timething wrong?
Here is my code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
If Target.Value = "Austin" Then
Range(Range("A" & Target.Row), Range("P" & Target.Row)).Copy _
Sheets("Austin").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
No, you did nothing wrong, you just want the code to do something that is not programmed.
Whenever a change is made in column A, that row (from column A to P) is being copied to the first available row in the specified sheet.
When you want changes to be made to sub sheets, when a change is made in column B to P on the master sheet, a unique value is needed to find the row on the sub sheets.
So my question would be: which column has a unique value that doesn't change?
An additional question would be: are those target values the only values to be entered in column A? If so, we can replace those 7 block of code by a single one.
I have successfully implemented the code but I have a small issue, What if i need to change the field in the main page to another value? Here i need to delete the entry from the sheet where the entry was first copied and copy it to the new sheet. Can you help please?
TrowaD2356
Posts Sunday September 12, 2010Registration dateModeratorStatus
May 24, 2018
Last seen
>
markafar1
Posts Wednesday June 14, 2017Registration date
June 14, 2017
Last seen -Jun 15, 2017 at 11:37 AM
Hi Markafar,
Your question is similar to Elmn's. So answer the questions I asked him in post #25.
Firstly, I've read this whole thread and I am really impressed at your level of commitment and the quality of your answers. Thank you so much! This thread has helped me more than any of the other 10 tabs I have open in trying to resolve my errors.
Having said that, I am stuck.
I have a spreadsheet with 6 columns A-F with column F triggering the change and thus the macro.
The code I've used from you:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
If Target.Value = "COCONUTS" Then Range(Range("A" & Target.Row), Range("F" & Target.Row)).Copy _ Sheets("COCONUTS").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
I'll try to be as clear as possible with my questions:
01) It seems that due to the fact this code runs whenever a change is made to the selected column, as soon as I select multiple rows on the main sheet ("Cargas") and then delete them/copy them I get an error 13. After this any changes I make to the front sheet results in the code replacing the value on the sheet Coconut instead of copying to a new row. I tried getting around this error with On Error Resume Next, and while I stopped seeing the error, the macro now replaces instead of copying. Do you have any ideas please? Have you come across this before?
02) Next, I am hoping to reach a point where any changes that are made to the front sheet then update the corresponding rows on the other respective tabs. There is a unique identifier in column D for every row. Do you have any ideas?
I hope this was clear and thank you so much for your help in getting me this far!
Wayne
I've been wanting to use the code you suggested. I input the target value at the end so everything in that row can be copied to another worksheet, but error 9 pops up and doesnt allow the code to work. I copy the code exactly as you wrote it here. I dont know What im doing wrong. Can you please help me?
TrowaD2356
Posts Sunday September 12, 2010Registration dateModeratorStatus
May 24, 2018
Last seen
>
aglo252
Posts Wednesday June 14, 2017Registration date
June 15, 2017
Last seen -Jun 15, 2017 at 11:43 AM
Hi Olga,
That would suggest that the target values are not the same as your sheet names. When in the code you refer to a sheet as 'ABC' while the sheet is actually called 'ABCD', Excel gets confused and pomps out an error 9.
So please check the sheet references and when you get stuck it is probably best to upload your file to know what is going on.
To do so use a free filesharing site such as www.speedyshare.com or ge.tt and post back the download link. Always be careful with sensitive data.
aglo252
Posts Wednesday June 14, 2017Registration date
June 15, 2017
Last seen
>
TrowaD2356
Posts Sunday September 12, 2010Registration dateModeratorStatus
May 24, 2018
Last seen -Jun 15, 2017 at 01:42 PM
Trowa,
I couldnt get it to work.
This is my sample data columns
Date Carrier Trailer# Loaded/Empty Seal# IMPO/EXPO Notes
I want to based it off the Carrier. Im trying to use this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
target.entirerow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub
Since the carrier Values are going to be more than 10 different carriers
I'm trying to create a workbook similar to what the others are working on. I want to copy each row (column A-F) into new worksheets based on column C (which has a dropdown menu), and I'd like it to update when new lines are added, or lines are changed. Column A (hidden) has unique entries.
The dropdown menu has 7 options (AS, BB, JS, AS BB, AS JS, BB JS, AS BB JS). Is it possible to copy any items that have AS alone or AS in combination with other initials into one sheet, and the same for the other initials? So, there would only be 3 destinations (AS or BB or JS). If the initials are in combination, I'd like the row to be moved to multiple destinations.
So the sub sheets have the same header as the main sheet?
Which cell in the row do you enter last (this doesn't have to be F)? Keep in mind that when you make a change and want to update the other sheet(s), you will have to re-confirm that cells contents to make the code do it's work.
Yes, they all have the same column headers. I will fill out column D last. That is the column that has the initials in it, as a dropdown menu. Sorry, in the previous post I had C listed as the column with initials, but I forgot to include the hidden column.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D")) Is Nothing Then Exit Sub
Dim mFind As Range
Target.EntireRow.Copy
If Target.Value Like "*AS*" Then
Set mFind = Sheets("AS").Columns("A").Find(Target.Offset(0, -3))
If mFind Is Nothing Then
Sheets("AS").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Else
mFind.PasteSpecial
End If
End If
If Target.Value Like "*BB*" Then
Set mFind = Sheets("BB").Columns("A").Find(Target.Offset(0, -3))
If mFind Is Nothing Then
Sheets("BB").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Else
mFind.PasteSpecial
End If
End If
If Target.Value Like "*JS*" Then
Set mFind = Sheets("JS").Columns("A").Find(Target.Offset(0, -3))
If mFind Is Nothing Then
Sheets("JS").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Else
mFind.PasteSpecial
End If
End If
Application.CutCopyMode = False
End Sub
I'm also very new to macro, so I don't know what I'm supposed to be adjusting!
My main sheet is titled "Project Timeline" and my other sheets are "Marketing", "Training" etc. the column in "Project Timeline" i've used is G, not H, so I adjusted that. But I'm not sure what else I'm supposed to adjust!
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("H:H")) Is Nothing Then Exit Sub
If Target.Value = "General" Then
Range(Range("C" & Target.Row), Range("H" & Target.Row)).Copy _
Sheets("General").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub
target.entirerow.Copy _
Sheets(target.value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
But as requested in my previous post, please be so kind to use an alternate file sharing method. Also make sure your query is clearly explained either in your file or in your next post.
talalsarvana4
Posts Tuesday September 5, 2017Registration date
October 2, 2017
Last seen
>
TrowaD2356
Posts Sunday September 12, 2010Registration dateModeratorStatus
May 24, 2018
Last seen -Oct 4, 2017 at 08:51 AM
Hi TrowaD
I am linking the 4shared file please have a look at it. hope it works this time.
TrowaD2356
Posts Sunday September 12, 2010Registration dateModeratorStatus
May 24, 2018
Last seen
>
talalsarvana4
Posts Tuesday September 5, 2017Registration date
October 2, 2017
Last seen -Oct 5, 2017 at 11:35 AM
Hi Talal,
Sorry for the inconvenience, but I like a file sharing site without any form of login method.
But I might not need the file if the following is correct:
Column E is used to enter Machine numbers.
These Machine numbers correspond to already created sheets.
Once a Machine number is confirmed, you want the entire row copied to the first available row of the respective sheet.
The following code does as described above:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mSheet As String
If Intersect(Target, Columns("E")) Is Nothing Then Exit Sub
mSheet = Target.Value
Target.EntireRow.Copy _
Sheets(mSheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub
To implement the code, right-click your main sheets tab and select view code. Paste the code in the big white field.
Hopefully this points you in the right direction, as I wouldn't want you to have to wait any longer then necessary. Thanks for your patience.
I want to transfer the data from Production Sheet to the respected machine Sheet as i enter the machine number in Column E. If there is any other way of doing it please let me know that also.
Thanks for your code.
I used the code segment successfully,. But, on reopening the file after saving with macro enabled, the code does not seem to run!
What should I do?
Davis
That is strange, saving your file shouldn't alter the way the code works in any way.
I wouldn't mind taking a look at your file to see what is going on.
Use a free file sharing site like www.speedyshare.com or ge.tt to upload your file and then post back the download link. Always be careful with sensitive information.
I'll get back to you next week, though.
Best regards,
Trowa
Problem solved.
I had to "Enable Content" Then it is working beautifully.
Sorry for the trouble.
Thanks
Davis