Autopopulating multiple sheets from one main sheetSolved/Closed

Question

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
System Configuration: Windows 7 / Chrome 24.0.1312.57

TrowaD · Accepted Answer

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 SubIf 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 IfEnd 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

Mikey · Answer

Thank you so much, it worked perfectly! My day just got 10x better thanks to you!

Soumyajyoti · Answer

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

TrowaD · Answer

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

TrowaD · Answer

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
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

TrowaD · Answer

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

TrowaD · Answer

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

TrowaD · Answer

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

cmjay · Answer

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

TrowaD · Answer

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

sleigh911 · Answer

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 · Answer

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

TrowaD · Answer

Hi Bern,

Sorry to keep you waiting.

Give the following code a try:
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

Best regards,
Trowa

TrowaD · Answer

Hi ns56,

Try this:
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)
Best regards,
Trowa

Autopopulating multiple sheets from one main sheet

14 responses

Similar discussions

Subscribe To Our Newsletter!