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!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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:

Trikson

Board Regular
Joined
Feb 10, 2014
Messages
61
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,753
Members
413,930
Latest member
Nela817

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
Top