Copy Check Box to Every 5th Cell with Command Button

[axi1985]

I have an issue which i can't solve.I wrote this code:
<code>Private Sub CommandButton2_Click()

Sheets("sheet2").OLEObjects("CheckBox1").Copy

Sheets("sheet3").Range("V7").PasteSpecial

End Sub</code> This code copy a checkbox from (sheet 2) to (sheet 3) starting from V7 cell. Now I want the next time I press the command button to paste the data to cell V12,next time to V17 etc. My vba knowledge is not very good as you can see.

 

[Domenic]

Try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton2_Click()

    [COLOR=darkblue]Dim[/COLOR] oOleObj [COLOR=darkblue]As[/COLOR] OLEObject[COLOR=darkblue][/COLOR]
    [COLOR=darkblue]Dim[/COLOR] rTarget [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] MaxRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    Sheets("sheet2").OLEObjects("CheckBox1").Copy
    
    MaxRow = 0
    [COLOR=darkblue]With[/COLOR] Sheets("sheet3")
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oOleObj [COLOR=darkblue]In[/COLOR] .OLEObjects
            [COLOR=darkblue]If[/COLOR] TypeName(oOleObj.Object) = "CheckBox" [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] oOleObj.TopLeftCell.Column = 22 [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]If[/COLOR] oOleObj.TopLeftCell.Row > MaxRow [COLOR=darkblue]Then[/COLOR]
                        MaxRow = oOleObj.TopLeftCell.Row
                        [COLOR=darkblue]Set[/COLOR] rTarget = oOleObj.TopLeftCell
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] oOleObj
        [COLOR=darkblue]If[/COLOR] rTarget [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]Set[/COLOR] rTarget = .Range("V7")
        [COLOR=darkblue]Else[/COLOR]
            [COLOR=darkblue]Set[/COLOR] rTarget = rTarget.Offset(5)
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    rTarget.PasteSpecial
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[axi1985]

Thanks too much...It's working perfect...