CODEWITHSUNDEEP
vbaexcel
mp.jpeg
mp.jpeg

Reputation: 197

Excel Macro to give Input Box, Create New Sheet, Copy Data from Original Sheet into New Sheet

I am trying to create a Macro that will Present an Input box once a button is clicked, simply saying "What would you like to call the new sheet?", then once the user has entered the name, Excel create a new sheet with that name.

However, I would like for the Data in the Original Sheet to be copied over to the new sheet that has been named by the user.

I have the Input box working, with this code:

Sub NewSheet()

Sheets.Add.Name = InputBox("What Would You Like to Call the New Sheet?")    

End Sub

Upvotes: 1

Views: 1777

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the code below:

    Sub NewSheet()

    Dim origSht             As Worksheet
    Dim destSht             As Worksheet

    On Error GoTo eHandle

    Set origSht = ActiveSheet

    Sheets.Add.Name = InputBox("What Would You Like to Call the New Sheet?")
    Set destSht = ActiveSheet

    origSht.Cells.Copy Destination:=destSht.Cells

Exit Sub

eHandle:

    MsgBox "You must name the new sheet"
    set origSht = nothing
    set destSht = nothing

End Sub

Upvotes: 7

Related Questions