# Taking cells from one workbook based on checkbox criteria of another workbook

#### RockandGrohl

##### Well-known Member
Hello all,

I have two worksheets, TW and PP.

In TW there is a userform that contains a variety of checkboxes, these determine the information I want to take from PP.

There are 12 checkboxes for each month of the year, then four category checkboxes.

As an example, if I select CBJAN, CBFEB and CBMAR, then CBJGGB and CBJGEU, I would want to find every Jan, Feb and Mar product where the category also matches JGGB and JGEU

Would I need to DIM the checkboxes and then match that to the dates in PP and categories? I'm totally new to using checkboxes.

Thank you!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### RockandGrohl

##### Well-known Member
I currently have my checkbox code as such:

Code:
``````Set TW = Worksheets("Tour Weighting 1")

Dim Lastrow As Long

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

TW.Activate

If CBJAN = True Then
Jan = "OK"
Else
Jan = "No"
End If

If CBFEB = True Then
Feb = "OK"
Else
Feb = "No"
End If

If CBMAR = True Then
Mar = "OK"
Else
Mar = "No"
End If

If CBAPR = True Then
Apr = "OK"
Else
Apr = "No"
End If

If CBMAY = True Then
May = "OK"
Else
May = "No"
End If

If CBJUN = True Then
Jun = "OK"
Else
Jun = "No"
End If

If CBJUL = True Then
Jul = "OK"
Else
Jul = "No"
End If

If CBAUG = True Then
Aug = "OK"
Else
Aug = "No"
End If

If CBSEP = True Then
Sep = "OK"
Else
Sep = "No"
End If

If CBOCT = True Then
October = "OK"
Else
October = "No"
End If

If CBNOV = True Then
Nov = "OK"
Else
Nov = "No"
End If

If CBDEC = True Then
Dec = "OK"
Else
Dec = "No"
End If

If CBJGGB1 = True Then
JGGB = "OK"
Else
JGGB = "No"
End If

If CBJGEU1 = True Then
JGEU = "OK"
Else
JGEU = "No"
End If

If CBJGE1 = True Then
JGE = "OK"
Else
JGE = "No"
End If

If CBJGSV1 = True Then
JGSV = "OK"
Else
JGSV = "No"
End If

Set PP = Workbooks.Open("H:\Sales\Price Panels\Price Panels 2019.xlsm", ReadOnly:=True)

PP.Activate

Range("A3").Activate

Do Until Cells(ActiveCell.Row, "A").Value = ""
If Cells(ActiveCell.Row, "B").Value <> "Active" Then
ActiveCell.Offset(1, 0).Activate
Else

End If

ActiveCell.Offset(1, 0).Activate
Loop``````

So now I have a way of either saying the checkbox was true or false, and if it's true then it's "OK", if not - "No"

If I check JAN and JG GB then

CBJAN = OK
CBJGGB1 = OK

I then open PP and start looking for rows that are only Active. If the row is Active and the value in column N (which is Jan, Feb, Mar, Apr etc) = an OK value, then it should look at the category in Y. If the category in Y is an ok value, then I just need to say activecell.row A.value is the Product code and activecell.row c.value is the product name, then paste them in TW and go down, then loop.

That very last part isn't the problem, I'm struggling with how to determine a bunch of variables and hold them, then check each row in PP against these variables for a match.

Say I check JAN, FEB and MAR in the userform, as well as JG GB and JG EU.

It needs to say JAN FEB and MAR are OK, all the other months are No. JG GB and JG EU are ok and the other products are No.

Then open PP, loop down each row and determine:
A) If the product is Active (column B)
B) If the product Month is one of the OK variables (column N)
C) If the product category is one of the OK variables (column Y)

If those three criteria are met, it just writes the value of particular cells to memory and then pastes them back on TW, then repeats the process until the end.

Hope this clarifies!

Replies
6
Views
355
Replies
0
Views
91
Replies
0
Views
426
Replies
3
Views
224
Replies
0
Views
171

1,195,994
Messages
6,012,748
Members
441,724
Latest member
Aalbid

### 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.

### Which adblocker are you using?

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

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