VBA Code Utilizing Checkboxes

[KLAALK]

Hi All,

Long time lurker/learner, after searching through the boards I am stuck on this one! My skillset it pretty basic but what I am trying to accomplish is the following:

Sheet1 = Column J is full of check boxes. Once the checkboxes are selected I want to select copy columns A to I of that specific row (data starts on row 3) and copy it to the next open row on the next sheet (QuoteDataSheet) starting on Cell B3. I need this to run through ~500-1000 rows of data/checkboxes on Sheet1 (depending on how long it would take to run).

Essentially I want to check a box to confirm I want to pursue a quote, once I check that box I want the data to move to another sheet and create a unique number for each quote without duiplicating data.

Seems simple, apparently not to simple for me!

Below is what I have so far and it is not working...

Sub MoveData()
Dim R As Integer, K As Integer
R = 1
K = 3
If ActiveSheet.CheckBoxes("Check Box " & R).Value = 1 Then
Range(Cells(K, "A"), Cells(K, "I")).Select
Selection.Copy
Sheets("QuoteDataSheet").Select
Columns("B").Find("", Cells(Rows.Count, "B")).Select.Paste
Range("B1").Select
Else

End If


Next K, R


End Sub


The error I get is Compile Error - Next without For.

Thank you for any help!

 

[NeonRedSharpie]

Sub MoveData()Dim R As Integer, K As Integer
R = 1
K = 3
For R = 1 To 1000 Step 1
    K = R + 2
    If ActiveSheet.CheckBoxes("Check Box " & R).Value = 1 Then
    Range(Cells(K, "A"), Cells(K, "I")).Select
    Selection.Copy
    Sheets("QuoteDataSheet").Select
    Columns("B").Find("", Cells(Rows.Count, "B")).Select.Paste
    Range("B1").Select
    Else
        'Do nothing
    End If
Next R




End Sub

That should fix your problem you're getting. If the code works I can't be sure. I also added in "to 1000" because why not. In later versions you should look at making that relative.

 

[KLAALK]

Thank you! This got me past the Next without For error. However I am now getting an error saying unable to get the Checkboxes property of the Worksheet class.

I assume I have labelled the check boxes funny in the line:
If ActiveSheet.CheckBoxes("Check Box " & R).Value = 1 Then

However I am not sure how else to name the checkboxes because I want to use the R variable to loop through all checkboxes..

As I said I have a pretty basic skillset which is obvious, thanks for any and all help!

 

[NeonRedSharpie]

I'm not sure how to reference checkboxes in a worksheet. Is there an option to set up a "linked cell" like there is in a userform? If there is, you can call upon that cell to see if it's true or false. I have a hard time looping through objects in a worksheet or userform.

(Note: I know you might not know the answer to this, just providing additional information.)

 

[KLAALK]

Yes, I can link the checkbox to a cell, but I only know how to do that manually, as in one checkbox at a time which would be very time consuming. I was hoping to reference through the checkbox name/number specifically and avoid cell linkage for so many checkboxes.

I appreciate any and all information!