Report

Autopopulating multiple sheets from one main sheet [Solved/Closed]

Ask a question Mikey - Last answered on Jun 19, 2017 at 10:48 AM by TrowaD
Hello,



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?

I greatly appreciate any and all help!
Thanks

Michael
See more 
Helpful
+3
plus moins
Hi Michael,

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?

Let me know if you get stuck somewhere.

Best regards,
Trowa
Davis- Apr 26, 2016 at 09:56 AM
Hi TrowaD,

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
TrowaD 2237Posts Sunday September 12, 2010Registration date ModeratorStatus June 22, 2017 Last seen - Apr 26, 2016 at 11:38 AM
Hi 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
Davis- Apr 27, 2016 at 07:28 AM
Hi Trowa

Problem solved.
I had to "Enable Content" Then it is working beautifully.

Sorry for the trouble.

Thanks
Davis
Helpful
+1
plus moins
Thank you so much, it worked perfectly! My day just got 10x better thanks to you!
Helpful
+1
plus moins
Hi Kaushlendra,

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?

Best regards,
Trowa
kaushal1234 4Posts Tuesday July 14, 2015Registration date July 21, 2015 Last seen - Jul 21, 2015 at 04:52 AM
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.

Thanks for your all help and guidance.

Regards
Kaushlendra
Helpful
+1
plus moins
Hi Kaushlendra,

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.

The choice is yours!

Best regards,
Trowa
Helpful
+1
plus moins
Hi Suzanne,

I'm still here :).

Maybe you implemented the code in the wrong place.
Right-click the "all" sheets tab, select View Code and paste the code in the big white field.

The other thing that I notice, is that you have Column B used for the sheet names, while the code looks only at column G for any changes.
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub

Change "G:G" to "B:B" to make the code look at column B.


You also say you want to copy columns A-F, but in the code it will copy A-G.
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy

Change the "G" into "F".


The destination for WellsFargo is different then the rest. It will not be pasted in column A but C. If this is supposed to be A then
Sheets("WellsFargo").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

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.



Does this solve your query?

Best regards,
Trowa
jwchestnut2 1Posts Tuesday May 16, 2017Registration date May 16, 2017 Last seen - May 16, 2017 at 01:07 PM
Trowa,

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.
Reply
TrowaD 2237Posts Sunday September 12, 2010Registration date ModeratorStatus June 22, 2017 Last seen - May 18, 2017 at 11:22 AM
Hi jwchestnut2,

Ok, for that request I need the following info:

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'.

Best regards,
Trowa
Reply
Leave a comment
Helpful
+0
plus moins
Trowa, Michael -

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)

End If

End Sub
Helpful
+0
plus moins
Hi Soumyajyoti,

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"

Best regards,
Trowa
mbearnstein37- Jul 8, 2015 at 03:48 PM
Hi Trowa,

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.

Thanks,
Matt
Helpful
+0
plus moins
Hi Matt,

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
kaushal1234 4Posts Tuesday July 14, 2015Registration date July 21, 2015 Last seen - Jul 16, 2015 at 08:32 AM
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)

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

You may download the file from below link:
https://www.dropbox.com/s/f7a9q46k1nkcms9/master%20file.xlsx?dl=0

Kindly let me know what could be the possible solution for it. Thanks is advance.
Helpful
+0
plus moins
Hi Kaushal,

Here is your code:
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.

Best regards,
Trowa
kaushal1234 4Posts Tuesday July 14, 2015Registration date July 21, 2015 Last seen - Jul 17, 2015 at 01:21 AM
Hi TrowaD

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.

Regards,
Kaushlendra
Acker- Apr 26, 2016 at 12:27 PM
TrowaD,

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?
Helpful
+0
plus moins
Hi Trowa,

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!

Best,

Chris
Helpful
+0
plus moins
Hi cmjay,

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


Best regards,
Trowa
Helpful
+0
plus moins
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)

ElseIf Target.Value = "Visa" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("Visa").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target.Value = "Mastercard" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("Mastercard").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target.Value = "WellsFargo" Then
Range(Range("A" & Target.Row), Range("G" & Target.Row)).Copy _
Sheets("WellsFargo").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

End If

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.

What did I do wrong on my code?

Thanks for any help you can provide!
Suzanne
TrowaD 2237Posts Sunday September 12, 2010Registration date ModeratorStatus June 22, 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.

Best regards,
Trowa
Reply
aglo25 2Posts Wednesday June 14, 2017Registration date June 15, 2017 Last seen - Jun 15, 2017 at 09:30 AM
Hi Trowa,

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?

Thanks,
Olga
Reply
TrowaD 2237Posts Sunday September 12, 2010Registration date ModeratorStatus June 22, 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.

Best regards,
Trowa
Reply
aglo25 2Posts Wednesday June 14, 2017Registration date June 15, 2017 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 tried uploading the file but it wouldnt let me

Thanks,
Olga
Reply
TrowaD 2237Posts Sunday September 12, 2010Registration date ModeratorStatus June 22, 2017 Last seen - Jun 19, 2017 at 10:48 AM
Hi Olga,

Your posts got deleted because 'http://ge.tt' is blacklisted. I didn't realize it was. Try using www.speedyshare.com or another free filesharing site.

If the sheet names in column B match the sheet names in your workbook, then I really need to take a look at your file to see what is going on.

Best regards,
Trowa
Reply
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!