Populate separate worksheets with data from main worksheet based on criteria met [Solved]

- - Latest reply: Jules_St-Germain
Posts
398
Registration date
Friday October 26, 2018
Last seen
November 26, 2018
- Nov 12, 2018 at 06:12 PM
Hello,

I'm making an accounting workbook and I've run into a problem.

On the first sheet "Sales Log" I input all of the transactions that the company does on a table that goes from B10-i10, and on down for every entry. One of the columns in the table is labeled "Room" and refers to which Sales room the revenue came from. There are 4 rooms (Exit, Room A, Room B, Room C) of the company and they each have a sheet in this workbook.

What I would like to do, is have each individual Room's sheet automatically populate with data when it's inputted onto the main "Sales Log" sheet, and have it based on the Room column in the table.

So basically, if the Room says "Room A", I would like for the entire row to be transferred to the sheet "Room A". And so on for each individual Room.

That way I don't have to copy and paste the hundreds of transactions to individual pages manually.
Any tips or suggestions?
See more 

Your reply

4 replies

Best answer
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
2
Thank you
Hello Bianca,

Have a look at the following thread. It may be a good option for you:-

https://ccm.net/forum/affich-1010285-master-sheet-to-affect-multiple-sheets#newanswer

It is a relatively simple code to follow and understand.

Change the target column in the code to the one which holds the relevant "Room".

The thread will also explain where and how to implement the code. You won't need a button with this code. It will automatically transfer data to the relevant sheet once the Room name is placed in any cell in the Room column. The Room name and sheet name must be exactly the same in spelling/punctuation for this to work.

Test it in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

Thank you, vcoolio 2

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM has helped 2553 users this month

Thank you! Trying it now!
I feel like an idiot with this. I wasn't able to figure that out, even after you simplified it... Here is the sample file. Could you have a look? https://www.dropbox.com/s/qzpsawuituexxwz/Report%20Template.xlsx?dl=0
Respond to vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
1
Thank you
Hi Bianca,

Your thanks and appreciation for the help you received is enough for me. I'm glad that I was able to help.

All the best.

Cheerio,
vcoolio.
Jules_St-Germain
Posts
398
Registration date
Friday October 26, 2018
Last seen
November 26, 2018
-
Another Excel problem bites the dust!

Thanks to our Italian friend vcoolio.
Respond to vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
0
Thank you
Hello Bianca,

After having a look at your workbook, it became obvious that the code in the other thread wasn't quite going to deal with the set out of your workbook. Hence, I've made a few changes to suit your set out. The amended code is as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim x As Long: x = Target.Row
        Dim lr As Long: lr = Sheets(Target.Value).Range("B" & Rows.Count).End(xlUp).Row + 1

If Intersect(Target, Columns("K:K")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False
        
        If lr < 9 Then lr = 9
        Union(Range("B" & x), Range("C" & x), Range("D" & x), Range("E" & x), Range("I" & x)).Copy
        Sheets(Target.Value).Range("B" & lr).PasteSpecial xlValues
        Sheets(Target.Value).Columns.AutoFit

Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


I'm assuming that the commission figure in each "Room" sheet is something that you type in manually.

Following is the link to your workbook with the code implemented. Just select a Room from the drop downs that you have created and you'll see that the relevant data is transferred automatically to the relevant individual sheet.

http://ge.tt/4lzFffs2

Follow the instructions in the other thread to implement the code into the Sales Log sheet module.

Test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.
I keep getting this error message when trying to add an entry in the main sheet : Run time error '9' Subscript out of range. What am I doing wrong

https://www.dropbox.com/s/9v6zjpsjt40ybin/Screen%20Shot%202018-11-10%20at%209.01.38%20AM.png?dl=0
Respond to vcoolio
Posts
1206
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 7, 2018
0
Thank you
Hello Bianca,

What am I doing wrong


Its not what you're doing wrong, it was an oversight on my part! (place embarrassment emoji here).

Here's the code again:-

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim x As Long: x = Target.Row
        Dim lr As Long

If Intersect(Target, Columns("K:K")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

        lr = Sheets(Target.Value).Range("B" & Rows.Count).End(xlUp).Row + 1
        If lr < 9 Then lr = 9
        Union(Range("B" & x), Range("C" & x), Range("D" & x), Range("E" & x), Range("I" & x)).Copy
        Sheets(Target.Value).Range("B" & lr).PasteSpecial xlValues
        Sheets(Target.Value).Columns.AutoFit

Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


You'll note that I have moved the last row (lr) declaration to its correct position in the code (line 13). I've also added a couple of extra lines(7 and 8) which will help guard against accidental inputs and empty strings in the target column creating new errors.

Read through the code then implement it in place of the previous one but, again, test it in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.
It works perfectly!! Thank you so much! This is going to save me hours :) How can I repay you??
Respond to vcoolio