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

Question

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?

vcoolio · Accepted Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

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

4 responses

Similar discussions

Subscribe To Our Newsletter!