The perpetual question: Auto-populating sheets based on criteria
Closed
NotExcelling
-
Feb 17, 2016 at 07:17 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Feb 19, 2016 at 05:22 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Feb 19, 2016 at 05:22 PM
Related:
- The perpetual question: Auto-populating sheets based on criteria
- Yahoo mail forgot password and security question - Guide
- Mom’s secret question - Guide
- Recover gmail account with security question - Guide
- Upside down question mark on keyboard - Guide
- Reset my facebook password using security question - Guide
3 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Feb 17, 2016 at 11:39 PM
Feb 17, 2016 at 11:39 PM
Hello NotExcelling,
Have a look at the following thread. I'm sure it is similar to your situation:-
https://ccm.net/forum/affich-873428-copy-the-data-of-a-row-based-on-identifying-the-data-in-a-cell
The code uses AutoFilter which is very quick.
The Poster never bothered to reply so I don't know if it worked for him/her.
As a learning exercise, I'll let you do some homework on this one but don't be afraid! Test it in a copy of your work book first.
- Copy and paste the headings into your new sheet first.
- You can remove line 16 from the code as in the other post, the copied data would have a column of blanks so we hid the column.
- Carefully read lines 7 & 8 and change the ranges to suit your situation (line 7).
- The code auto filters and searches Column I for blanks ("") as the criteria on line 8. You need to change................
Call back and let us know how you go. If you get stuck, we'll help you sort it out.
Good luck!
Cheerio,
vcoolio.
Have a look at the following thread. I'm sure it is similar to your situation:-
https://ccm.net/forum/affich-873428-copy-the-data-of-a-row-based-on-identifying-the-data-in-a-cell
The code uses AutoFilter which is very quick.
The Poster never bothered to reply so I don't know if it worked for him/her.
As a learning exercise, I'll let you do some homework on this one but don't be afraid! Test it in a copy of your work book first.
- Copy and paste the headings into your new sheet first.
- You can remove line 16 from the code as in the other post, the copied data would have a column of blanks so we hid the column.
- Carefully read lines 7 & 8 and change the ranges to suit your situation (line 7).
- The code auto filters and searches Column I for blanks ("") as the criteria on line 8. You need to change................
Call back and let us know how you go. If you get stuck, we'll help you sort it out.
Good luck!
Cheerio,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Feb 19, 2016 at 04:42 AM
Feb 19, 2016 at 04:42 AM
Hello NotExcelling,
I knew that you could do it!
To create a button:-
I usually create my own button by selecting the "Insert" tab (at the top of your sheet) and then selecting "Shapes". A drop down will appear with many shapes to select from. I usually select a rectangle of sorts. After selecting the shape you want, go to wherever you want on your sheet and draw the shape (your cursor will have changed to a cross-hair by then). You will notice in the ribbon that the "Shape Styles" will light up (there are lots of things to play with here!). Click on the down arrow and select the style you want. The button that you have drawn will change to your selected style. Type a name in the button and then format it for centre alignment, font colour and font size.
That's the button done (best to practise all this on a blank sheet first).
When you have finished creating your button, right click on it. From the menu that appears, select "assign macro". The Macro dialogue window will open. Select the name of the desired macro in the larger box and then click OK. Save the sheet again. Your button should now do its thing. When you have done all your data entry and typed "Complete" in whichever cell in Column A, just click on the button to execute the code.
A command button can also be created via the Developer tab but we'll leave this for now. We'll get you used to being "creative" first!
The code could also be changed to a Worksheet_Change event which means that every time you enter "Complete" in a cell in Column A and click away (or use down arrow or hit enter) the code will automatically execute straight away for each "Complete" entry. No button needed.
Until next time.
Cheerio,
vcoolio.
I knew that you could do it!
To create a button:-
I usually create my own button by selecting the "Insert" tab (at the top of your sheet) and then selecting "Shapes". A drop down will appear with many shapes to select from. I usually select a rectangle of sorts. After selecting the shape you want, go to wherever you want on your sheet and draw the shape (your cursor will have changed to a cross-hair by then). You will notice in the ribbon that the "Shape Styles" will light up (there are lots of things to play with here!). Click on the down arrow and select the style you want. The button that you have drawn will change to your selected style. Type a name in the button and then format it for centre alignment, font colour and font size.
That's the button done (best to practise all this on a blank sheet first).
When you have finished creating your button, right click on it. From the menu that appears, select "assign macro". The Macro dialogue window will open. Select the name of the desired macro in the larger box and then click OK. Save the sheet again. Your button should now do its thing. When you have done all your data entry and typed "Complete" in whichever cell in Column A, just click on the button to execute the code.
A command button can also be created via the Developer tab but we'll leave this for now. We'll get you used to being "creative" first!
The code could also be changed to a Worksheet_Change event which means that every time you enter "Complete" in a cell in Column A and click away (or use down arrow or hit enter) the code will automatically execute straight away for each "Complete" entry. No button needed.
Until next time.
Cheerio,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Feb 19, 2016 at 05:22 PM
Feb 19, 2016 at 05:22 PM
Hello NotExcelling,
If you go back to that other thread, in the code, you'll notice a line that is in green font. That line will delete the "used" data from sheet 1 once the transfer of data is complete.
To activate that line, just remove the apostrophe at the begining of the line. I am assuming that you have copied it over to your code. If not, do so but make sure that it is in the same place in your code as the other.
If you prefer to keep the data in sheet 1, the other option is to refresh sheet 3 each time a transfer is done. So, in your code, just above this line:-
add this line:-
("UsedRange" is the data set. Offset(1) ensures that your headings are not deleted).
Either way, you should not end up with duplicates in sheet 3.
I hope that this helps.
Cheerio.
vcoolio.
If you go back to that other thread, in the code, you'll notice a line that is in green font. That line will delete the "used" data from sheet 1 once the transfer of data is complete.
To activate that line, just remove the apostrophe at the begining of the line. I am assuming that you have copied it over to your code. If not, do so but make sure that it is in the same place in your code as the other.
If you prefer to keep the data in sheet 1, the other option is to refresh sheet 3 each time a transfer is done. So, in your code, just above this line:-
With ActiveSheet
add this line:-
Sheet3.UsedRange.Offset(1).ClearContents
("UsedRange" is the data set. Offset(1) ensures that your headings are not deleted).
Either way, you should not end up with duplicates in sheet 3.
I hope that this helps.
Cheerio.
vcoolio.
Feb 18, 2016 at 02:57 PM
Thanks for your awesome advice!