Populate separate worksheets with data from main worksheet based on criteria met
Solved/Closed
Related:
- Populate separate worksheets with data from main worksheet based on criteria met
- Transfer data from one excel worksheet to another automatically - Guide
- Tmobile data check - Guide
- How to automatically transfer data between sheets in Excel - Guide
- Mint mobile data not working ✓ - Network Forum
- Nokia.mobi/entry/van/main/n8-00 - Nokia Forum
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
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.
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
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Nov 11, 2018 at 07:16 PM
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.
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.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Nov 10, 2018 at 06:30 AM
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:-
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.
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
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Nov 10, 2018 at 06:29 PM
Nov 10, 2018 at 06:29 PM
Hello Bianca,
Its not what you're doing wrong, it was an oversight on my part! (place embarrassment emoji here).
Here's the code again:-
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.
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.
Nov 9, 2018 at 04:10 PM
Nov 9, 2018 at 07:25 PM