I need a way to create a new sheet with a command button. Then copy a template sheet and paste the template in the new sheet. Then rename the sheet with the text in the textbox. Any ideas would be greatly appreciated. Thanks guys.
Auto copy/paste and rename sheet from text box and command button
-
-
-
Re: Auto copy/paste and rename sheet from text box and command button
Hello,
Paste the following code into VB and link it to the command button and it will grab the name from a textbox labeled textbox1. Then it will rename and copy everything from the template sheet now for the template sheet you will need to rename the sheet to whatever you call that sheet and it will work. Hope This Helps!!!
Code
Display MoreSub SheetSub() Dim SheetName1, SheetName2 As String SheetName1 = TextBox1.Text SheetName2 = ActiveSheet.Name Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = SheetName1 Sheets("Place Template Sheet Name Here").Select 'will copy everything Cells.Select Selection.Copy Sheets(SheetName1).Select ActiveSheet.Paste 'goes back to where you started Sheets(SheetName2).Select End Sub -
Re: Auto copy/paste and rename sheet from text box and command button
Thank you very much it works perfect! I have been tinkering for hours and kept coming up empty. Thanks again!!
-
Re: Auto copy/paste and rename sheet from text box and command button
It would be a good idea to verify that a worksheet, having the same name as that entered into the textbox, does not exist.
You did not mention it, but the assumption is you would not want to copy the button to the new sheet.Code
Display MoreOption Explicit Sub Create_New_Sheet() Dim NewSheetName As String, ws As Worksheet Dim msg As String Application.ScreenUpdating = False If Sheet1.TextBox1 <> vbNullString Then NewSheetName = Sheet1.TextBox1.Value msg = "Worksheet " & NewSheetName & " already exists" & vbLf msg = msg & "Please enter a different name" For Each ws In ThisWorkbook.Worksheets If ws.Name Like NewSheetName Then MsgBox (msg), vbExclamation Sheet1.TextBox1 = vbNullString Exit Sub End If Next ws Worksheets.Add.Name = NewSheetName Sheet1.Cells.Copy Worksheets(NewSheetName).Paste Application.CutCopyMode = xlCopy ActiveSheet.Buttons.Delete End If Application.ScreenUpdating = True End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!