The perpetual question: Auto-populating sheets based on criteria

NotExcelling - Feb 17, 2016 at 07:17 PM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Feb 19, 2016 at 05:22 PM

I realize this question has been asked in a million iterations, but my understanding of programming is so nonexistent that I can't confidently adapt the existing answers to fit my needs. Please bear with me.

I have a "Master" data sheet with all the existing data entries, but want to make a copy of only those marked "Complete" in the first column into a separate sheet entitled "Complete". What is the best way to go about doing this? Also is it possible to transfer the column heads (located in row 2) onto the new sheet automatically as well, or is it better to manually copy them over and have the sheet auto-populate below?

Please let me know if I can clarify anything.
Thanks in advance!

3 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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:-

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!

Excellent! I got it to work. Now, I really don't understand this at all, but will the code keep updating as I add new entries into the first page or do I need to hit "run" to make it update the new complete data sets? Can I make a button to run this code?

Thanks for your awesome advice!
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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.

Thanks again! That worked great except now I have double copies of all the complete rows that were already copied over. Is there a line I could add to the code to clear the contents of Sheet3 before I copy the completed ones over or only update the new ones?

(Slightly less) NotExcelling
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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:-

With ActiveSheet

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.