[Solved] Moving data from sheet1 range(b8:b47) to sheet2 if checkboxes are checked = true

[Gwolfe7]

I have checkboxes in range(G8:G47) that returns a TRUE value if checked & changes the fill color and font to Bold in range(B8:B47) of the text within that range. If the checkbox is checked I’d like to move the text in B8:B47 to sheet2 starting at A1 down to A47. I’d also like to delete the blanks this will cause in sheet2 as within the range not all checkboxes will be checked by the user.

Thanks for your help!

 

[Aniruddah Alam]

Dear Gwolfe7,
Welocome to Exceldemy forum. Thanks for posting your problem. Based on your requirements, I have developed a VBA code that will move the value in a cell on column B only if the corresponding checkbox on the cell of column G is checked. This task can be accomplished effortlessly by simply clicking on a button. You can find the exact procedure illustrated in the GIF provided below.

As you can see, I have used a Helper column (column H) to store the information if the corresponding checkbox is selected or not. You can easily do that by linking each check box to the corresponding cell on column H.
Based on the True/False value on column H, the corresponding values on column B will be copied to Sheet2 beginning from cell A1.

I am attaching the .xlsm file. Feel free to reach us if you need any more assistance.
Regards
Aniruddah
Team Exceldemy

 

[Gwolfe7]

This is excellent, thanks again for your reply & code. I appreciate your mention of replying again with any questions.
With warm regards...

 

[Gwolfe7]

Dear Gwolfe7,
Welocome to Exceldemy forum. Thanks for posting your problem. Based on your requirements, I have developed a VBA code that will move the value in a cell on column B only if the corresponding checkbox on the cell of column G is checked. This task can be accomplished effortlessly by simply clicking on a button. You can find the exact procedure illustrated in the GIF provided below.
View attachment 1074
As you can see, I have used a Helper column (column H) to store the information if the corresponding checkbox is selected or not. You can easily do that by linking each check box to the corresponding cell on column H.
Based on the True/False value on column H, the corresponding values on column B will be copied to Sheet2 beginning from cell A1.

I am attaching the .xlsm file. Feel free to reach us if you need any more assistance.
Regards
Aniruddah
Team Exceldemy

Dear Aniruddah,
I tried the code after thanking you but I'm receiving an error upon trying to run it. Does "I" need to be defined as a variable? I have made some slight changes to the Sheet names and ranges (was using G rather than H for the helper column.) I have attached the error message.
Thanks

 

[Aniruddah Alam]

Dear Aniruddah,
I tried the code after thanking you but I'm receiving an error upon trying to run it. Does "I" need to be defined as a variable? I have made some slight changes to the Sheet names and ranges (was using G rather than H for the helper column.) I have attached the error message.
Thanks

Dear Gwolfe7,
Usually, VBA doesn't require the declaration of variables beforehand. However, you may try the following code where I have explicitly declared each variable.

Sub MoveCheckedData()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range
    Dim chkBox As CheckBox
    Dim cell As Range
    Dim copied_value As Variant
    Dim i As Long
    
    Set ws1 = ThisWorkbook.Sheets("Interface3") 'change it accordingly
    Set ws2 = ThisWorkbook.Sheets("Sheet3") 'change it accordingly
    
  'clearing entire column A of ws2
  ws2.Range("A:A").Clear
  Set rng = ws1.Range("G8:G47") 'Helper column range, change it accordingly
  i = 0
  For Each cell In rng
        If cell = True Then
            i = i + 1
            copied_value = cell.Cells(1, -4).Value ' column  B is 4 cells left to column G, change it accordingly if you change the helper column
            ws2.Range("A" & i).Value = copied_value
        End If
    Next cell
End Sub

After trying, kindly share your feedback.

Regards
Aniruddah
Team Exceldemy

 

[Gwolfe7]

This worked perfectly. Again, thanks for your time & subsequent review.
With warm regards...

 

[Gwolfe7]

May I please ask for one other function???

While using the "Move" button on your 1st reply (I created a button that says "Click for a Summary").

I'd like that click to open Sheet3 to review the items moved to that sheet.

Thanks again!

 

[Gwolfe7]

This seemed to work when added to your 2nd code suggestion after Next cell.

Sheets("Summary").Activate

Thanks for your review!

 

[Aniruddah Alam]

This seemed to work when added to your 2nd code suggestion after Next cell.

Sheets("Summary").Activate

Thanks for your review!

Hey there! It's great to hear that you've got the command line for showing the Summary sheet after copying the data into it. If you need any more help or have any questions, please feel free to reach out to us anytime. Our Exceldemy team is always happy to assist you in any way we can!

Best Regards
Aniruddah
Team Exceldemy

 

[Gwolfe7]

If I’d like to copy the formatting (cell color=RBG #7B240B/font color=white) of Interface3 to ws2 (named Summary) what and where might I insert the coding? Would it be right after ws2.Range("A:A").Clear ?

 

[Aniruddah Alam]

If I’d like to copy the formatting (cell color=RBG #7B240B/font color=white) of Interface3 to ws2 (named Summary) what and where might I insert the coding? Would it be right after ws2.Range("A:A").Clear ?

For that, you can put the following 2 lines of code below ws2.Range("A" & i).Value = copied_value line:

ws2.Range("A" & i).Font.Color = RGB(255, 255, 255) ' RGB value of White color
ws2.Range("A" & i).Interior.Color = RGB(123, 36, 11) ' RGB value of #7B240B

Regards
Aniruddah

 

[Gwolfe7]

Good Day! I'm back again. Can you tell I'm a newbie to Excel?
The last code provided works well to change the cell background/font color however I'd like to see the full string from the Sy's Choices sheet. I've attached my code & screen captures. A full text from the sheet I'm copying it from should be copied along a11. If I widen column A I do see the full text however.

Thanks for your review!

 

[Aniruddah Alam]

Good Day! I'm back again. Can you tell I'm a newbie to Excel?
The last code provided works well to change the cell background/font color however I'd like to see the full string from the Sy's Choices sheet. I've attached my code & screen captures. A full text from the sheet I'm copying it from should be copied along a11. If I widen column A I do see the full text however.

Thanks for your review!

For that, you need to AutoFit column A of the Summary sheet. To do that, add the following line below Sheets("Summary").Activate line:

Sheets("Summary").Columns(1).AutoFit

Regards
Aniruddah

 

[Gwolfe7]

YOU! are the man...That worked perfectly.

Thanks again!!!