VBA - If the UserForm checkbox is ticked, search for value in Sheet and copy row

Trikson

Board Regular
Joined
Feb 10, 2014
Messages
61
Hi guys,

Haven't been logging-in here for some time as my current role doesn't involve as much VBA as the previous ones. That also means that I've got rusty and now I found myself in a dead end.

I have a following template sheet with data entries:

ABCDE
1R1DataDataDataData
2R2DataDataDataData
3R3DataDataDataData
4R4DataDataDataData
5R5DataDataDataData

<tbody>
</tbody>

There is a userform with checkboxes associated with it. Based on what checkboxes are ticked by the user I need the macro to copy specified rows from template sheet into new one.

Example 1:

User ticks Checkbox 1 only and clicks "OK" on the form. Macro searches for value R1 in column A and copies entire row where R1 is found into new sheet. After macro is done, new sheet should look like:

ABCDE
1R1DataDataDataData

<tbody>
</tbody>

Example 2:

User ticks Checkbox 1, 3 and 5 and clicks "OK" on the form. Macro searches for values R1, R3, and R5 in column A and copies entire rows where the R1, R3 and R5 values are found into new sheet. After macro is done, new sheet should look like:

ABCDE
1R1DataDataDataData
2R3DataDataDataData
3R5DataDataDataData

<tbody>
</tbody>


Any tips on how to tackle this will be appreciated.

Thank you!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
While the post gives plenty of detail for the sheets, it is not clear on how your Checkbox relates to the value to be searched. But since you are searching for a value, rather than a range address, I would suggest using the Find method to loceate the cell with the value.
Code:
Dim fn As Range
Set fn = Range("A:A").Find("R1", , xlValues, xlWhole)
   If Not fn Is Nothing Then
      fn.EntireRow.Copy Sheet2.Range("A1") 'For example
   End If
 
Last edited:
Upvote 0
Let me give you more details on the checkbox:

There are ~20 checkboxes, by default all with value false (unticked). The userform is there to help user to decide which entries they will need in their template (by ticking the boxes they need).

Each checkbox corresponds to one specific data row from the template sheet, which are marked by the R# value in column A (checkbox 1 = R1, checkbox 2 = R2, so on and so forth).

I need the code to be something along these lines:

Code:
If Checkbox1.Value = True Then 'find value R1 in template sheet and copy entire row into new sheet, then proceed to check value on remaining checkboxes

hope it gives more details now, but let me know if you need something more to go on.

Will try to tie the find code JLGWhiz provided into the checkboxes later today and report on the results.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top