Moving data from one tab to another via checkboxes

[Tim Flenniken]

I am extremely new to VBA, so please bear with me. I have searched the Excel forum but didn't see another question like mine (unless I'm not searching the correct terms). I have an Excel 2013 workbook with multiple tabs and I need to move data from the "TSA" tab to the "DRT" tab if the end-user decides to do so ("TSA" is the population of data and "DRT" would be the resulting sample set for testing purposes). The issue is that I have 30 Unit numbers (think vehicles) in the "TSA" and each Unit has up to 90 lines of data or more. I want a way to have the end-user choose which lines for each of the Unit numbers to move over to the "DRT" tab without having to create 2700+ checkboxes and 2700+ macros. Would it be easier to declare each of the Unit numbers as its own array and then create a Loop to go through the 30 arrays and if the checkbox is checked move that specific row of data to the "DRT" tab under the corresponding Unit number? (And if so, can someone show me how to get that Loop started?)
Thank you!

 

[cerfani]

you are basically saying you want users to be able to select which of the 90 lines for the 30 IDs to move... so in fact your users do need to answer 2700 questions. If you do it by ID then you assume the user always want to move every line of an id and now this is 30 questions.

Why not come up with logic that reduces the number of questions... like if there is a value 1 somewhere, then always move that. Use the data to make decisions instead of someone reading and thinking. You can program all the decisions.

Unless you really dont want every line considered, then explain what you need cause in your question you said you need to ask 2700 questions but dont want to. Now you need logic to reduce this.

What do you mean by checkboxes? Are you working with forms or some type of controls? Do you just mean writing a character into a cell?

 

[Tim Flenniken]

Hi, cerfani. Thanks for replying and I apologize for not elaborating. So, the "TSA" tab contains rows of data for up to 30 Unit numbers,depending on the number of Units the end user wants to input, and this data is a list of all of the trips that Unit (or those Units) has/have traveled for an entire quarter. The quarter's worth of data then becomes the population and from that population the end user would select a number of those trips for each Unit to do some reasonableness testing and those rows would need to go to the "DRT" tab. Right now they are choosing the rows and then copying and pasting from one tab to another, but I have been tasked with making it an automated process to copy and paste the chosen rows in order to make the audit process more efficient and less time-consuming.

When I spoke about check boxes, I was referring to ActiveX Control check boxes. My thought was to create a checkbox at the end of each row of data on the "TSA" tab and the end user can make the decision of which of the rows to send to the "DRT" tab and just check the checkbox and have it done for them. I'm just trying to save myself from having to create 2700 check boxes and the subsequent macros. Or I could also just place the check boxes at the end of the rows in "TSA" and add a Command Button for each Unit that would look at the check boxes as a Boolean and copy and paste over each checked row for each Unit.

 

[cerfani]

I see, ok that makes more sense. You can probably create these controls dynamically at runtime. I dont have much experience with activex but forms for example, you could add controls to a form at runtime.

You may want to allow users to set the autofilters and then your macro simply moves all visible rows... so no need for controls