Command button to create a new sheet

[Ian1976]

Hi All,

Is it possible to have a command button on a sheet which when pressed creates a copy of the sheet and puts it at the end off all the existing sheets but also has the command button on it, so when I've finished filling in the new sheet I can keep creating new sheets?

Thanks

 

[Yongle]

One way, an active-X command button with this code

Me.Copy After:= Sheets(Sheets.Count)

 

[Ian1976]

Thanks mate, will this also transfer the button onto the new sheet to then create others, sorry not on a pc at the moment! ??

 

[Norie]

Add a button from the Forms toolbar to the sheet and when you are prompted to Assign Macro select New.

You should now see something like this.

Option Explicit

Sub Button1_Click()
    
End Sub

Update that code to this to copy the sheet the button is on to the end of the workbook.

Option Explicit

Sub Button1_Click()
Dim sh As Worksheet

    Set sh = ActiveSheet
    
    sh.Copy after:=Sheets(Sheets.Count)
    
End Sub

Now whenever you click the button the sheet, and the button, will be copied.

The code itself will not be copied but each new button will have this same code assigned to it.

 

[Ian1976]

Thanks both options work great, is it possible to be able to name the newly created sheet as "New Log" then "New Log1" ect....

Thanks Guys! Much appreciated!

 

[harveya915]

Would this work if I have forms and buttons on a sheet that i want for the code to transfer over to the new sheet as well?

 

[Joe4]

Would this work if I have forms and buttons on a sheet that i want for the code to transfer over to the new sheet as well?

No, VBA code will not copy over with it.
If you want to know how to do that, that is really a brand new question which warrants its own thread.

 

[harveya915]

No, VBA code will not copy over with it.
If you want to know how to do that, that is really a brand new question which warrants its own thread.

Will do, thank you.

 

[Yongle]

is it possible to be able to name the newly created sheet as "New Log" then "New Log1" ect....

This code is for active-x command button

Place VBA in sheet module

Private Sub CommandButton1_Click()
    Me.Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = GetName
End Sub

Private Function GetName() As String
    Dim x As Long, n As String
    n = "New Log"
    If SheetExists(n) Then
        Do
            x = x + 1
            If Not SheetExists(n & x) Then Exit Do
        Loop
        n = n & x
    End If
    GetName = n
End Function

Private Function SheetExists(aName As String) As Boolean
    On Error Resume Next
    Dim sh As Worksheet: Set sh = Sheets(aName)
    If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

 

[Yongle]

Form Control button equivalent (see post#9 for active-x)

Place code in standard module

Sub Button1_Click()
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = GetName
End Sub

Private Function GetName() As String
    Dim x As Long, n As String
    n = "New Log"
    If SheetExists(n) Then
        Do
            x = x + 1
            If Not SheetExists(n & x) Then Exit Do
        Loop
        n = n & x
    End If
    GetName = n
End Function

Private Function SheetExists(aName As String) As Boolean
    On Error Resume Next
    Dim sh As Worksheet: Set sh = Sheets(aName)
    If Err = 0 Then SheetExists = True Else SheetExists = False
End Function