Hi All.
I've been struggling with an issue for a while now - hope there are some bright heads here, that can help me.
I would like the following function:
1. I have 11 checkboxes on worksheet 1. Each checkbox corresponds to a column in a table on worksheet 2 (so 11 columns in total).
2. If I push a botton on worksheet 1, the program looks for the "state" (checked/unchecked) of the checkboxes;
3. If the first checkbox is checked, it should look for the value in column L on worksheet 2 - but only from row 26. If it finds a cellvalue of 4 OR 5, it should copy all cells from column B to AC of that line (if more cells have a value 4 or 5 in line L it should copy them all.
4. The copyed data PLUS formatting should then be pasted on worksheet 1 from cell B23 in the last available row - BUT ONLY if the line/cells isn't already copied to worksheet (I would like a list of unique entries)
5. If the second checkbox is checked, it goes through the same routine as above, but this time it looks for 4 or 5 i column M on worksheet 2
6. And so on with the remaining 9 checkboxes on worksheet 1 and columns in workseet 2 (checkbox 3 looks in column N, checkbox 4 in column O, checkbox 5 in column P....checkbox 11 looks in column V)
Hope it all makes sense!
Here is what I have so far - if there is a more "smooth"/optimized way to do this, feel free to comment on that:
Private Sub CommandButton1_Click()
With Sheets("Worksheet 1").Rows("23:10000").ClearContents
End With
'This part is for checkbox 1 (worksheet 1) and column L (worksheet 2)
If Worksheets("Worksheet 1").Cells(5, 1).Value = True Then
Worksheets("Worksheet 2").Activate
b = Worksheets("Worksheet 2").Cells(Rows.Count, 2).End(xlUp).Row
For j = 26 To b
If Worksheets("Worksheet 2").Cells(j, 12).Value = 4 Or 5 Then
'This copies the entire row. I would like it to just copy from column B to AC
Worksheets("Worksheet 2").Rows(j).Copy
Worksheets("Worksheet 1").Activate
c = Worksheets("Worksheet 1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("Worksheet 1").Cells(c + 1, 2).Select
ActiveSheet.Paste
End If
Next
End If
'Here the same code as above will go, but for checkbox 2
'Here the same code as above will go, but for checkbox 3
'...
'Here the same code as above will go, but for checkbox 11
Worksheets("Worksheet 1").Activate
Application.CutCopyMode = False
End Sub
Workbook is attached. Hope some of you can help - if not with all just parts of the code then
Bookmarks