Copy Rows If Relative Checkboxes Are Checked - VBA

**Storm08** · 01-10-2018, 04:43 AM

Hi All!

I'm looking for some VBA code that fulfils the following:

On Sheet1, each row has a check box in Column A. If a checkbox is checked from Column A, it's corresponding Row copied to Sheet2 when a button is pressed.

The other Columns on Sheet1 do not have check boxes in them, only certain columns (B, C, F, G, O, P) are to be copied to Sheet2 when the button is pressed.

When copying to Sheet2, the gaps in-between the Columns should be deleted.

Thanks in advance,

Joe

EDIT: I have tried using code from previous threads, but cant seem to make it work!

**Storm08** · 01-10-2018, 08:11 AM

I have got this piece of code:

Please Login or Register  to view this content.

Only trouble is this copies all columns. How would I modify it to only copy the columns B, C, F, G, O & P?

**mjr veverka** · 01-10-2018, 09:27 AM

Originally Posted by Storm08

... On Sheet1, each row has a check box in Column A ...

? 1,048,576 check boxes ?

**Storm08** · 01-11-2018, 06:57 AM

Originally Posted by porucha vevrku

? 1,048,576 check boxes ?

Maybe not that many.

There will be up to 300 rows populated.

**nigelog** · 01-11-2018, 07:09 AM

I have two pieces of code that will probably, when combined, sort this but please attach a workbook as I have no inclination of re creating....a sample would do with dummy data

**Storm08** · 01-11-2018, 12:51 PM

Here's the sample document.

If you could make it work in this document, that'd be brilliant.

**Storm08** · 01-12-2018, 10:04 AM

Is this enough information to go on?

**Storm08** · 01-15-2018, 08:16 AM

Don't mean to bump this, but I would like to get a solution to this asap.

**Storm08** · 01-23-2018, 07:10 AM

Anybody?

**nigelog** · 01-26-2018, 11:15 AM

Finally had a chance to look at this.....there is also code to add the checkboxes as they have to be linked to the rows to reference them

Please Login or Register  to view this content.

**Storm08** · 02-20-2018, 01:00 PM

Originally Posted by nigelog

Finally had a chance to look at this.....there is also code to add the checkboxes as they have to be linked to the rows to reference them

Please Login or Register  to view this content.

Hi nigelog,

Thank you very much for helping me out with this. It is working brilliantly!

I have a question if you dont mind? If I wanted to change the row the range started from, how would this be done? I have realised that I would need to copy from row 3 instead of row 8.

All the best,
Joe

**nigelog** · 02-20-2018, 01:19 PM

chane the references to "8" to "3" in the copy section of the code.... thanks for the rep joe

Please Login or Register  to view this content.

**Storm08** · 02-20-2018, 01:39 PM

Originally Posted by nigelog

chane the references to "8" to "3" in the copy section of the code.... thanks for the rep joe

Please Login or Register  to view this content.

Ah right! Simple enough!

Thank you.

Also, one last query I have. One of the columns that is copied contains formula. On Sheet1 this is in Column "P", and contains the following: "=F8*O8". When being copied into Sheet2, this moves into Column "G" and should become: "=D8*F8", due to the formula being relative. But what I actually get on Sheet2 in Column "G" is: "#REF!*F8". This suggests that I should be using an absolute formula, but I shouldn't need to due to the cells needing to change when being copied to Sheet2.

Any ideas?

P.S - You're welcome for the rep! Well earned!

**nigelog** · 02-21-2018, 04:55 AM

I'll have a look

Please Login or Register  to view this content.

**Storm08** · 02-21-2018, 04:57 AM

Thanks very much!

**nigelog** · 02-21-2018, 05:29 AM

Change this part for new last row value on sheet2

Please Login or Register  to view this content.

**mjr veverka** · 02-21-2018, 07:06 AM

And add a dot to the code:

Please Login or Register  to view this content.

And move "If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False" up to "With ... End With", in the form:

Please Login or Register  to view this content.

Without it, if you run a macro from a different sheet than 'Sheet1', strange things can happen ...

From Ms description:
"Using this property without an object qualifier is equivalent to using ActiveSheet.Rows"
It also applies to other things ...

**nigelog** · 02-21-2018, 07:15 AM

Well spotted perucha, ta

Please Login or Register  to view this content.

**Storm08** · 02-21-2018, 01:36 PM

Thanks very much. This has corrected the #REF! errors. However, there is more than one formula in the end column. Is there another way to achieve this? Sorry I hadn't explained his before.

Also, when I un-check a check box on Sheet1, I get the run-time error '1004':We can't do that to a merged cell.

I have attached an up to date file to demonstrate.

**nigelog** · 02-22-2018, 05:03 AM

Hi
it is difficult to see all the connections in your sample, please enter representative data in the first two sub heading layouts as you have some formatted for currency yet others are calculated and not formatted. Also rename columns as they would appear as that would help seeing what is going on

ta

i cant recreate the merged cell problem, works fine here

**Storm08** · 02-22-2018, 05:15 AM

Sorry, I removed too many headings!

**nigelog** · 02-22-2018, 05:23 AM

In the first sheet I would like to see a few rows as they are populated - Unit is carried to second sheet but is involved in no calculations - it could be feet inches or sq miles - be handy to know what was what

Edit

As the second sheet is basically a summary report (not an editable document-that should be in the first sheet) then I would suggest no formulas in sheet 2 only values. Some of your calculations involve cells not carried over at all.

**Storm08** · 02-22-2018, 05:53 AM

Unit is not a cell that is included in any calculation, it just shows what the Qty is of, e.g - hours, days, metres, etc. It can vary.

The calculation between the columns is Total = Qty x Rate. In the "Total" column, each sub-heading row should calculate the sum of the cells under that sub-heading.

Also, if a new row is inserted into Sheet1, is it possible for the sheet to automatically add a new checkbox for that row?

**nigelog** · 02-22-2018, 08:46 AM

To enter rows I could only delete and add checkboxes again to keep link

I had no time to try ans store/retrieve the checkboxes previous value so I checked them all (not a great solution but temporary)

Update client copies only values now - should not be a working document but a report

right click for normal excel options and look for "control" - click on it for option menu to appear on sheet1

hope that helps

Edit few niggles ironed out

**Storm08** · 02-22-2018, 01:02 PM

Hi again,

This is working almost perfectly now! I have to thank you for coding these functions, and also for your patience with me!

I have had a thought about the formula in the total column not working. Is it possible to do something along the lines of:

If row fill colour = rgb(246, 247, 248), calculate the sum of cells below. The range end is when the code gets to another cell with the fill colour of rgb(246, 247, 248). If this is possible and you put this code after the one you came up with previously and use an If Else statement so it doesn't run on blank rows, I think that'd be it.

Everything else is spot on and is working well. It's just that Sheet2 needs to be able to work on its own.

EDIT:
Ah ha! Here's what I was looking for:

Please Login or Register  to view this content.

**nigelog** · 02-22-2018, 01:35 PM

You can do what what you are looking for by setting ranges with "special cells" such as formatting. A few people on here I have seen have a lot of knowledge in this area such as jindon

Probably best if you start a new thread with just that request "how to sum cells to the next special cell" and utilise that later in the code to select the special cells in sheet 2 and enter a formula....

**Storm08** · 02-22-2018, 01:48 PM

Ah right, excellent. Thanks for pointing me in the right direction, I'll start a new thread and reference this one.

Thank you very much for all of your help, I really appreciate it!

**nigelog** · 02-23-2018, 08:33 AM

Sorted

summing cells on headers will automatically shrink and grow as lines are added deleted

inserted rows get an linked checkbox

all data now gets copied to Sheet2

Last 3 checkboxes on sheet 1 must always be clicked. The headers sum the values by looking for the next cell of the same format - that light shaded line needs to be above the darker shaded one to avoid it including the sheet total

**Storm08** · 03-08-2018, 04:51 AM

Hi nigelog,

Apologies for the delay, I hadn't received an email alert for the thread.

Thanks very much for the update, I'll have a go and let you know how I get on.

**Storm08** · 03-13-2018, 04:53 AM

Cant seem to access the "Control Panel" when right clicking. The code for it doesn't seem to be there either?

**nigelog** · 03-13-2018, 08:08 AM

Hi, try this workbook. Must have linked wrong one

just noticed this leaves data in the clipboard - add the line

Please Login or Register  to view this content.

before the end of the copy rows sub

**Storm08** · 03-14-2018, 05:10 AM

Hi again,

This is absolutely spot on! Can't thank you enough for all of your work on this!

If you don't mind, I have one last function that I have found would need to be added to this sheet. Is that okay?

Is it possible to recall which checkboxes were selected when adding the checkboxes back?

**nigelog** · 03-14-2018, 05:18 AM

Hi
I thought of that when I was doing but didn't have time to look at. The checkboxes are linked to a cell on the row and record true or false. That list would have to to be written to either a helper column or an array and replaced to their correct cell, as in taking taking into account what row was added etc. I will take a look at but I think now on this occasion you should start a new thread asking that specific question. It may be answered a lot faster than I could manage.

**Storm08** · 03-14-2018, 06:57 AM

Originally Posted by nigelog

Hi
I thought of that when I was doing but didn't have time to look at. The checkboxes are linked to a cell on the row and record true or false. That list would have to to be written to either a helper column or an array and replaced to their correct cell, as in taking taking into account what row was added etc. I will take a look at but I think now on this occasion you should start a new thread asking that specific question. It may be answered a lot faster than I could manage.

Hi,

No worries at all. The theory behind the recall function makes perfect sense, but I understand how time consuming writing and testing code can be! I'll start a new thread for this item.

Thanks very much for all of your help, it's greatly appreciated!

Kind Regards,
Joe

**nigelog** · 03-14-2018, 07:36 AM

I reckon the best way is to only add a checkbox the row that is highlighted when a row inserted (rather than deleting them all and replacing. I am trying to reference the inserted row but can't quite get the syntax
this doesn't work

Please Login or Register  to view this content.

I suggest trying to adjust the worksheet change sub to not reference the add and delete subs in module 7 but to just add a checkbox to the inserted row

Please Login or Register  to view this content.

let me know how you get on

**nigelog** · 03-14-2018, 09:28 AM

This will add a checkbox to only the inserted row so all others keep their value (with thanks to Arkadi on here), rather than deleting all and re adding all checkboxes

Please Login or Register  to view this content.

if this sorts your request can you mark as solved. Rgds

**Storm08** · 03-14-2018, 11:24 AM

Hiya,

Thanks for providing a solution! it works when inserting the check box, but after it's done that, it selects all of the check boxes rather than leaving them in the state they were before.

**nigelog** · 03-14-2018, 12:13 PM

Code above definitely does not do that

**Storm08** · 03-14-2018, 12:51 PM

It doesn't in the document you just posted. Must have been me putting it somewhere incorrect.

However, when I try to click the "Client Update" button, it says "We can't do that to a merged cell".

**nigelog** · 03-14-2018, 01:15 PM

There are no merged cells in that workbook so the error must be in the workbook you have put the code into. Generally merged are nothing but grief - use centre across selection instead - or find another way around using them

Edit: the merged cells in your original sample workbook are C4:C5 remove the merging and should be ok

Edit: They are in workbook as well so its not that

Edit: Code changed to check that checkbox links are correct when rows are entered....

**rara2425** · 05-08-2018, 06:56 AM

pls inbox your excel sheet, i will support you.

**kmomchev** · 05-08-2018, 09:29 AM

Hi,

I can help you as well. Is Storm082.xlsm mentioned above, the last version you are working on?

Best, Konstantin

Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Re: Copy Rows If Relative Checkboxes Are Checked - VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copying Rows Represented By Checked Checkboxes

[SOLVED] My checkboxes will not stay checked.

Bookmarks

Bookmarks

Posting Permissions