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

[RockandGrohl]

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!

 

[RockandGrohl]

I currently have my checkbox code as such:

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!