Help with check box macro on new sheet

[Production Planner]

Hi ladies and gents,

I am having trouble getting the macro for the checkboxes on my template to copy across when I make a new sheet.

I currently have a macro that copies the template for me and asks for a new name.

Sub Picture3_Click()

    Dim sName As String
    Dim wks As Worksheet
    Worksheets("TEMPLATE").Copy after:=Sheets(Worksheets.Count)
    Set wks = ActiveSheet
    Do While sName <> wks.Name
        sName = Application.InputBox _
          (Prompt:="Enter new worksheet name")
        On Error Resume Next
        wks.Name = sName
        On Error GoTo 0
    Loop
    Set wks = Nothing
End Sub

This works fine but the issue lies in that when a check box on the new form is checked it does not do as it does on the template sheet.

here is the code for the template check box.

Private Sub CheckBox1_Click()

    With Sheet12
        If CheckBox1.Value Then
            Sheet12.Range("B4").Copy .Range("D4")
            .Range("B4").ClearContents
            

        End If
    End With
End Sub

The only thing I can see is that when the template is copied the code for the check box does not change the sheet name ie:Sheet12 to the number of the new sheet.

Is there anything I can do to over come this.

Any help would be appreciated.

Thanks in advance.

 

[AlphaFrog]

You don't need to specify a sheet if you want to reference the cells on the active sheet. If you are clicking on a checkbox and its code runs, then the sheet the checkbox is on is the active sheet.

I think this should work for your situation...

Private Sub CheckBox1_Click()

    If CheckBox1.Value Then Range("B4").Cut Destination:=Range("D4")
    
End Sub

 

[Production Planner]

Thanks for that, works a treat when making a new sheet from the template.

The only issue I have now is I get a #ref error further along my spreadsheet as cell (I4) =D4-H4. (So i can work out what is still left in stock after allocating it to a customer)


I tried to combine both what you have shown me and what I was using previously but I am getting a debug when clicked.

It is probably something fairly simple that I am missing.

Again help would be appreciated.

This is what I have come up with

Private Sub CheckBox1_Click()

    If CheckBox1.Value Then Range("B4").Copy .Range("D4")
            .Range("B4").ClearContents
            
End Sub

 

[AlphaFrog]

You don't need the periods in front of the Range statments. Those are only needed when used with a With statement. Remove the red periods.

Private Sub CheckBox1_Click()

    If CheckBox1.Value Then Range("B4").Copy [COLOR="Red"].[/COLOR]Range("D4")
            [COLOR="Red"].[/COLOR]Range("B4").ClearContents
            
End Sub

 

[Production Planner]

You don't need the periods in front of the Range statments. Those are only needed when used with a With statement. Remove the red periods.

Private Sub CheckBox1_Click()

    If CheckBox1.Value Then Range("B4").Copy [COLOR="Red"].[/COLOR]Range("D4")
            [COLOR="Red"].[/COLOR]Range("B4").ClearContents
            
End Sub

Thanks for that AlphaFrog.

Works a treat.

Cheers