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

Solved/Closed
biancacabo - Nov 8, 2018 at 02:04 PM
 Anonymous User - 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?

4 responses

vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Nov 9, 2018 at 12:16 AM
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.
2
Thank you! Trying it now!
0
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
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Nov 11, 2018 at 07:16 PM
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.
1
Another Excel problem bites the dust!

Thanks to our Italian friend vcoolio.
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Nov 10, 2018 at 06:30 AM
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.
0
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
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Nov 10, 2018 at 06:29 PM
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.
0
It works perfectly!! Thank you so much! This is going to save me hours :) How can I repay you??
0