Macro for Copying cells based on Checkbox

[vid]

Hi!!

I don't have much experience with macros and need help!!!!

I have checkboxes in column G. The user can select as many checkboxes as they wish. Once he/she has checked all the boxes they want, the values corresponding to them in column A should be copied to another worksheet.

eg.. just to clarify! if the check boxes in G7, G10, G25 have been selected, the value of the cells A7, A10, A25 should be copied to "Sheet3".

Please help...

Thanks a ton!!

 

[vid]

to clarify!

Actually what I need is that as and when I select a checkbox, the value in Column A for that row should get copied to "Sheet3". When I uncheck the box, it should get deleted from "Sheet3"... Please help! I'm clueless...

 

[PaulJR]

What sort of checkbox is it? Is it one created from the Forms toolbar, that is linked to a cell? OR is it from the Control toolbar that drives code?

 

[vid]

Hi!

I created the checkboxes from "control" in the "developer" tab.

 

[PaulJR]

From what you describe, I believe it would be easier to use the other type of checkbox from the "Forms" toolbar. This would eliminate the need for code - you could use a simple formula instead:

The "Forms" checkbox links to a cell, lets say it links to cell H7. When you check the checkbox, H7 will be TRUE. Then, all you need on sheet 3 is a formula like this:

=IF(Sheet1!H7=TRUE,Sheet1!A7,"")

 

[vid]

Hi!!

This thing has been driving me mad.... I've tried a lot of different things but ntn seems to work! Please help..

I have a sheet(sheet1) with about 1000 entries in column A. Each cell in column B must have a check box.

When any checkbox is selected, the value of the corresponding cell a should be copied over to another sheet(sheet2).

For eg.. if I select B3, B5, B100 in sheet1, in sheet2 value of cells A1,A2,A3 should be equal to the values of cells A3,A5,A100 in sheet1.

I hope this is making sense..

I have tried using both (form and activeX) types of checkboxes. However I am not able to accomplish this.

The problem with what you suggested is that instead of appearing in Cells A1,A2,A3 in sheet2, the value will appear in A3,A5,A100.

Besides for 1000 values I must link each checkbox individually! or is there a better way to do it which i dunno about?

I would prefer a system where I can select as many checkboxes as i want and then click one button which would make the required values appear in sheet2!

Please please please help.. this thing is really driving me mad!!!

 

[PaulJR]

If you just want a list to appear as A1, A2, A3 etc regardless of what you select in sheet 1, can you use a pivot table? This would give you a list and can be set to filter on the cells you select.

Just a sidenote, but it sounds like your checkboxes might also be a bit painful, as you have to have so many of them. Rather than use checkboxes you could use an Excel drop down list, it's easy to create (Data - Validation - 'Allow List' etc) and you can just copy and paste it down for as many rows as you like.

 

[vid]

Nope I can't use pivot table and cannot even use a drop down!

This has to be possible!!! please help

 

[Brianwarnock]

I agree with Paul that setting up all of those tick boxes must be a pain. Why not just use your mouse and Ctrl key to select the cells then a simple macro like

ActiveWindow.RangeSelection.Copy
Worksheets("sheet2").Range("A1").PasteSpecial
Application.CutCopyMode = False

will do the rest.

Brian

 

[chergh]

Putting 1000 checkboxes will bloat the size of your spreadsheet and in the long term result in corruption. Use what brian recommended

 

[vid]

Thanks for your suggestions It helped me a lot..