Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Selecting rows with check box and copying to them to other sheet

0

Hi,

I have rows with checkbox  at the begining   and

I like to copy that rows if checkbox selected than paste  to  sheet 2  

Thank you

Answer
Discuss

Discussion

Please post a copy of your workbook. You can remove all data except the column captions (which you can replace with random words). Leave all check boxes in place. Only one sheet is required. Sheet2 (the destination sheet) must exist in your workbook unless it has the same structure as the source. You should state if copied rows must be appended to existing rows of Sheet2 or replace them.
Variatus (rep: 4889) Nov 22, '18 at 9:32 pm
Thank you very much for your response,
There are two sheets  OP and QT
Rows has checkboxes on OP and if they are checked than they have to append 
on QT .Also when we are on QT sheet ,if check box is again not checked  status that row have to delete.
I have posted Excel file .
Quequeg (rep: 4) Nov 24, '18 at 2:34 pm
Add to Discussion

Answers

0
Selected Answer

Sorry Quequeg, but I shall not entertain your request for code. Instead I shall try to help you improve your workbook.

There are two reasons why I don't want to work with your current setup. One is that your checkboxes are a source of problems. Copy, for example, row 8 from OP to QT. You will see that Checkbox 13 from row 7 is copied in addition to Checkbox 14 from row 8. This is because checkboxes are not, strictly speaking, part of any row. Instead, they are attached to specific cells. The cell to which checkbox 13 is attached is in row 8. Therefore it is copied when row 8 is copied. When you check box 13 contents of row 8 would be copied to the other sheet because technically Checkbox 13 is in row 8.

Observe that each checkbox has a unique name. When it is copied to another sheet it takes the name with it. That name is then unique on the other sheet. But you intend to copy and delete. Sooner or later you will either run out of checkbox numbers or a name duplication will occur. Then any code running at the time will crash.

The second reason is that your workbook is clearly thrown together for the purpose of creating a question. It serves that purpose perfectly. However, it isn't equally suitable to contain an answer. Any code written exactly as you request will be useless to you because your real-life worksheets look different. Code is very exact. Therefore code should only be written when all design changes have been done. After that, when there is a design change code will also have to be adjusted every time. I don't want to write code which needs to be adjusted before it can be used. That would be a waste of my time.

It appears that you would like to have a list of items on sheet QT which are marked in sheet OP and create a total for those items. If that is what you want you should say so. It wouldn't be done the way you seem to imagine. Instead, such a list would better be prepared at the moment when you activate the QT sheet, as opposed to modifying it every time you click on one of the checkboxes. There would be two ways to modify it. You could either add or delete items on that list directly, or you could go back to OP and select or deselect items there and the list would be updated when you switch back to QT. This method would do away with any problems related to copying checkboxes because checkboxes would not be copied.

The other suggestion I would like to make is to do away with the checkboxes altogether. They aren't intended to be used in large quantity, one per row. Unless your OP list almost never changes, you may never be able to gain complete control of which row a checkbox refers to. The alternative would be to create a validation dropdown with with two choices (False/True, Yes/No, Select/Deselect, whatever). However, since your workbook would have code in it anyway I would recommend a click toggle. When you click on the cell the word "Selected" appears, and when you click again the cell becomes blank. Note that such action can only be encoded once it is exactly known which cells may be clicked to trigger this effect.

Once your system has been fully designed I shall help you with the code. Bear in mind, however, that this is a Q & A forum, meaning one question, one answer per thread. The task of designing is different from that of automating. Therefore it will require a separate thread. Please limit correspondence in this thread to the design phase only.

Discuss

Discussion

Thank you very much your valuable guidence.
I need work on  and study.
As you recomended I have started to use  dropdown validation with choices.
Than I could try also toggle
Quequeg (rep: 4) Nov 25, '18 at 4:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login