Copy & paste sheet in same workbook with VBA?

August 23, 2011 at 01:06:45
Specs: Windows XP
I am new to VBA and would like some help on the following please:

Could anyone tell me if it is possible to copy a worksheet and then paste it into the same workbook with a different name, using VBA? I would like to create an excel workbook that has 1 master document at the start which can then be duplicated by pressing a macro button. The idea being that everytime a customer called for a quote for work, the excel book could be opened, the button pressed, and a new blank form would be inserted at the end of the workbook. The form would then just be filled out and printed.
Also is it possible to have the sheet name appear in one of the cells automatically? (this is not as important but would be nice)

I am using Microsoft Office Excel 2007.

Any help on this would be greatly appreciated.

See More: Copy & paste sheet in same workbook with VBA?

Report •

August 23, 2011 at 06:31:12
Your request can be done, but there a few things to deal with:

The following code will ask the user for a sheet name and then:

1 - Check to see if the new sheet name already exists. If it does, a new name will be requested.
2 - Make sure the new sheet name doesn't contain any invalid characters and that there are no other problems with creating the new sheet. If there is, a new name will be requested.

Note: If any other error besides an invalid character (probably the most common issue) occurs the same message will be displayed. That's why it says to call "Technical Support" if the problem continues.

It does not check to see if any cells contain more than 256 characters which may or may not be an issue with your workbook. Let me know if that needs to be dealt with.

Once the new sheet is created, the Sheet Name will be placed in A1.

Sub AddSheet()
On Error GoTo errHandler
'Get name for new sheet
  shtName = Application.InputBox _
            ("Please Enter Name For New Sheet" & _
            vbCrLf & vbCrLf & _
            "Click Cancel To Quit")
'Check for existing sheet name
  For shtChk = 1 To Sheets.Count
   If Sheets(shtChk).Name = shtName Then
    MsgBox "That Sheet Name Already Exists" & _
        vbCrLf & vbCrLf & _
        "Please try again."
      GoTo getName
   End If
'Quit if canceled
   If shtName = False Then Exit Sub
'Add sheet and rename it
     Sheets("Master").Copy After:=Sheets(Sheets.Count)
     Sheets(Sheets.Count).Name = shtName
'Present Error Message
  If Err.Number <> 0 Then
     MsgBox _
     "There was a problem creating your new sheet." & _
     vbCrLf & vbCrLf & _
     "Please make sure the Sheet Name does not contain any invalid characters." & _
     vbCrLf & vbCrLf & _
     "If the problem persists please contact Technical Support."
'Delete new sheet if it was created
    If Sheets(Sheets.Count).Name = "Master (2)" Then
      Application.DisplayAlerts = False
      Application.DisplayAlerts = True
    End If
' Put Sheet Name in A1
  Sheets(sheet.count).range("A1") = shtName
  End If

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 6, 2011 at 01:42:16
Thank you very much!!!

Report •

Related Solutions

Ask Question