Results 1 to 8 of 8

Copy worksheet using VBA

This is a discussion on Copy worksheet using VBA within the Excel Questions forums, part of the Question Forums category; I am trying to copy an existing worksheet using VBA . I am able to do this however I would ...

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    1

    Default Copy worksheet using VBA

    I am trying to copy an existing worksheet using VBA. I am able to do this however I would like to add validation within the code that first checks if the worksheet name already exists and if it does show a MsgBox stating the worksheet already exists then loops until a valid name is entered.

    Any help would be greatly appreciated.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Copy worksheet using VBA

    Hi rbergeron:

    Welcome to the Board!

    Do you want to copy a Workbook or a Worksheet? It appears that you have done some work on this already -- how about posting your code for what you have so far, and then indicate where do you want to go from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default WELCOME TO THE BOARD!

    Is this what you are looking for?

    Sub Copy_Sheet()
    Dim NewName As String
    Dim Sh As Worksheet
    ****NewName = ActiveSheet.Name
    DuplicateSearch:
    ****For Each Sh In Workbooks("Book2.xls").Worksheets
    ********If UCase(Sh.Name) = UCase(NewName) Then
    ************NewName = InputBox(NewName & " already exists.**Please enter a new name.", "Enter New Name")
    ************GoTo DuplicateSearch
    ********End If
    ****Next Sh
    ****ActiveSheet.Copy Workbooks("Book2.xls").Worksheets(1)
    ****ActiveSheet.Name = NewName
    End Sub
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default Re: Copy worksheet using VBA

    Phantom:

    Your code will error if the Cancel button is clicked, or OK is clicked with an empty input string, or if an illegal naming character is attempted.

    This macro might cover most of the bases, long-winded as it is. Modify for the Sheet1 name being used as an example for the sheet you want to copy.

    Sub AddSheetz()

    'First, jump through the validation hoops
    Dim AddSheetQuestion As Variant

    'Define the application input box question
    showAddSheetQuestion:
    AddSheetQuestion = Application.InputBox("Please enter the name of the sheet you want to add," & vbCrLf & _
    "or click the Cancel button to cancel the addition:", _
    "What sheet do you want to add?")

    'Cancel or the X was clicked
    If AddSheetQuestion = False Then
    MsgBox "You clicked the Cancel button." & vbCrLf & "No new sheet will be added.", 64, "Cancel was clicked."
    Exit Sub

    'OK was clicked without anything being entered
    ElseIf AddSheetQuestion = "" Then
    MsgBox "You clicked OK but entered nothing." & vbCrLf & vbCrLf & _
    "Please type in a valid sheet name." & vbCrLf & "Otherwise, you must click Cancel to exit." & vbCrLf & vbCrLf & _
    "Click OK and let's try again.", 48, "Hmmm...that didn't make sense..."
    GoTo showAddSheetQuestion

    End If

    'See if a worksheet exists that is named as the new name being attempted to add.
    'We want this code to error, because if it does, it will mean no such sheet exists
    'so we can complete this macro.
    On Error Resume Next
    Worksheets(UCase(AddSheetQuestion)).Activate
    If Err.number <> 9 Then
    Err.Clear
    MsgBox "A worksheet already exists that is named " & AddSheetQuestion & "." & vbCrLf & vbCrLf & _
    "Please click OK, verify the name you really" & vbCrLf & _
    "want to add, and try again." & vbCrLf & vbCrLf & "Sheet addition cancelled.", 48, "Sorry, that name already taken."
    GoTo showAddSheetQuestion
    Exit Sub
    End If

    'Error trap #2 for naming syntax error
    On Error GoTo ErrorHandler1

    'Here's the actual sheet addition code
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    End With
    'Add and name the new sheet
    Worksheets.Add
    With ActiveSheet
    .Name = AddSheetQuestion
    .Move After:=Worksheets(Worksheets.count)
    End With

    'Make the Template sheet visible, and copy it
    With Worksheets("Sheet1")
    .Visible = xlSheetVisible
    .Activate
    End With
    Cells.Copy
    'Re-activate the new worksheet, and paste
    Worksheets(AddSheetQuestion).Activate
    Cells.Select
    ActiveSheet.Paste
    With Application
    .CutCopyMode = False
    .Goto Range("A1"), True
    End With

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With

    'Inform the user the macro is completed
    MsgBox "The new sheet name ''" & AddSheetQuestion & "'' has been added.", 64, "Sheet addition successful."
    Exit Sub

    'If a sheet naming syntax occurs:
    ErrorHandler1:
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    MsgBox "You entered a character that cannot be part of a sheet name." & vbCrLf & _
    "Sheet names cannot contain '':'', ''/'', ''\'', ''?'', ''*'', ''['', or '']''.", 16, "Name syntax error."
    GoTo showAddSheetQuestion
    Exit Sub
    End Sub

  5. #5
    MrExcel MVP phantom1975's Avatar
    Join Date
    Jun 2002
    Location
    Omaha, Nebraska
    Posts
    3,962

    Default Re: Copy worksheet using VBA

    I'll admit I sorta cranked out a solution without adding any error handling. I thought that I should have used Application.Inputbox, but had already posted it and didn't bother editing. The illegal characters escaped me completely though. Thanks for picking it up.
    Silly Billy was here....

    ***************** EXCEL/VB NEWBIES ARE MY FAVORITE! *****************

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default Re: Copy worksheet using VBA

    I want to follow up on this thread to thank Richie (UK) for PMing me this morning, telling me he received an error when he tested my code in a certain input sequence. Here's what he wrote to me, and then how he modified the code to handle that error:

    In the code as posted try the following sequence of events:
    1. On the first pass enter an invalid filename (I used /).
    2. Then on the second pass enter a normal name (I used bob).
    This through an error on the line "Worksheets(UCase(AddSheetQuestion)).Activate" - (Error 9). Something to do with the double error-checking I suspect but I couldn't quite pin it down. I revised the checking for an existing sheetname to a function approach and it now seems to work OK (see below).


    Sub AddSheetz()

    'First, jump through the validation hoops (need Variant to error-check)
    Dim AddSheetQuestion As Variant

    'Define the application input box question
    showAddSheetQuestion:
    AddSheetQuestion = Application.InputBox _
    ("Please enter the name of the sheet you want to add," & vbCrLf & _
    "or click the Cancel button to cancel the addition:", _
    "What sheet do you want to add?")

    'Cancel or the X was clicked
    If AddSheetQuestion = False Then
    MsgBox "You clicked the Cancel button." & vbCrLf & _
    "No new sheet will be added.", 64, "Cancel was clicked."
    Exit Sub

    'OK was clicked without anything being entered
    ElseIf AddSheetQuestion = "" Then
    MsgBox "You clicked OK but entered nothing." & vbCrLf & vbCrLf & _
    "Please type in a valid sheet name." & vbCrLf & _
    "Otherwise, you must click Cancel to exit." & vbCrLf & vbCrLf & _
    "Click OK and let's try again.", 48, "Hmmm...that didn't make sense..."
    GoTo showAddSheetQuestion

    End If

    'See if a worksheet exists that is named as the new name being attempted to add.
    If SheetExists(CStr(AddSheetQuestion)) Then
    MsgBox "A worksheet already exists that is named " & AddSheetQuestion & "." _
    & vbCrLf & vbCrLf & _
    "Please click OK, verify the name you really" & vbCrLf & _
    "want to add, and try again." & vbCrLf & vbCrLf & "Sheet addition cancelled.", _
    48, "Sorry, that name already taken."
    GoTo showAddSheetQuestion
    End If

    'Error trap for naming syntax error
    On Error GoTo ErrorHandler1

    'Here's the actual sheet addition code
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    End With

    'Add and name the new sheet
    Worksheets.Add
    With ActiveSheet
    .Name = AddSheetQuestion
    .Move After:=Worksheets(Worksheets.Count)
    End With

    'Make the Template sheet visible, and copy it
    Worksheets("Sheet1").Cells.Copy

    'Re-activate the new worksheet, and paste
    Worksheets(AddSheetQuestion).Paste

    With Application
    .CutCopyMode = False
    .Goto Range("A1"), True
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With

    'Inform the user the macro is completed
    MsgBox "The new sheet name ''" & AddSheetQuestion & "'' has been added.", _
    64, "Sheet addition successful."

    Exit Sub
    'If a sheet naming syntax occurs:
    ErrorHandler1:
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    MsgBox "You entered a character that cannot be part of a sheet name." & vbNewLine & _
    "Sheet names cannot contain the following:-" & vbNewLine & _
    "'':'' , ''/'' , ''\'' , ''?'' , ''*'' , ''['' , or '']''.", _
    16, "Name syntax error."
    On Error GoTo 0
    GoTo showAddSheetQuestion

    End Sub

    Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
    'Boolean function assumed to be False unless set to True
    End Function


    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''

    If anyone gets other errors please post what you did to get them. Thanks again Richie for seeing that error.

  7. #7
    New Member
    Join Date
    Jun 2013
    Posts
    1

    Default Re: Copy worksheet using VBA

    Hey I am a brande new member to the forum even though i have been searching the forum for a good while and so far i could not find an answer to my question here or on google. I know this is an old post and applogise but i have a question... I also appolgise if i missed a post with the answer. I have been using the below code for quite some time and itís worked flawlessly until recently. I recently added a pie chart to my template and now that i have done that this does not work 100%. The pie chart on the copied sheet is linked to the sheet name (datasource) of my template. I have played around with excel and when i manually copy the template sheet the pie chart datasource updates. I do not know VBA that well but what i assume (and i could be very wrong) the code below is copying the cells of my template and pasting them to the new template. Is there an easy way to modify the copy to update the name of the pie chart to the correct datasource? I only have one pie chart on my template so it would just need to update the one chat.

    Thanks for your help and I apologies in advance for the "noobish" question.

    Thanks for your time,
    Shane

    Quote Originally Posted by Tom Urtis View Post
    I want to follow up on this thread to thank Richie (UK) for PMing me this morning, telling me he received an error when he tested my code in a certain input sequence. Here's what he wrote to me, and then how he modified the code to handle that error:

    In the code as posted try the following sequence of events:
    1. On the first pass enter an invalid filename (I used /).
    2. Then on the second pass enter a normal name (I used bob).
    This through an error on the line "Worksheets(UCase(AddSheetQuestion)).Activate" - (Error 9). Something to do with the double error-checking I suspect but I couldn't quite pin it down. I revised the checking for an existing sheetname to a function approach and it now seems to work OK (see below).


    Sub AddSheetz()

    'First, jump through the validation hoops (need Variant to error-check)
    Dim AddSheetQuestion As Variant

    'Define the application input box question
    showAddSheetQuestion:
    AddSheetQuestion = Application.InputBox _
    ("Please enter the name of the sheet you want to add," & vbCrLf & _
    "or click the Cancel button to cancel the addition:", _
    "What sheet do you want to add?")

    'Cancel or the X was clicked
    If AddSheetQuestion = False Then
    MsgBox "You clicked the Cancel button." & vbCrLf & _
    "No new sheet will be added.", 64, "Cancel was clicked."
    Exit Sub

    'OK was clicked without anything being entered
    ElseIf AddSheetQuestion = "" Then
    MsgBox "You clicked OK but entered nothing." & vbCrLf & vbCrLf & _
    "Please type in a valid sheet name." & vbCrLf & _
    "Otherwise, you must click Cancel to exit." & vbCrLf & vbCrLf & _
    "Click OK and let's try again.", 48, "Hmmm...that didn't make sense..."
    GoTo showAddSheetQuestion

    End If

    'See if a worksheet exists that is named as the new name being attempted to add.
    If SheetExists(CStr(AddSheetQuestion)) Then
    MsgBox "A worksheet already exists that is named " & AddSheetQuestion & "." _
    & vbCrLf & vbCrLf & _
    "Please click OK, verify the name you really" & vbCrLf & _
    "want to add, and try again." & vbCrLf & vbCrLf & "Sheet addition cancelled.", _
    48, "Sorry, that name already taken."
    GoTo showAddSheetQuestion
    End If

    'Error trap for naming syntax error
    On Error GoTo ErrorHandler1

    'Here's the actual sheet addition code
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    End With

    'Add and name the new sheet
    Worksheets.Add
    With ActiveSheet
    .Name = AddSheetQuestion
    .Move After:=Worksheets(Worksheets.Count)
    End With

    'Make the Template sheet visible, and copy it
    Worksheets("Sheet1").Cells.Copy

    'Re-activate the new worksheet, and paste
    Worksheets(AddSheetQuestion).Paste

    With Application
    .CutCopyMode = False
    .Goto Range("A1"), True
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With

    'Inform the user the macro is completed
    MsgBox "The new sheet name ''" & AddSheetQuestion & "'' has been added.", _
    64, "Sheet addition successful."

    Exit Sub
    'If a sheet naming syntax occurs:
    ErrorHandler1:
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
    MsgBox "You entered a character that cannot be part of a sheet name." & vbNewLine & _
    "Sheet names cannot contain the following:-" & vbNewLine & _
    "'':'' , ''/'' , ''\'' , ''?'' , ''*'' , ''['' , or '']''.", _
    16, "Name syntax error."
    On Error GoTo 0
    GoTo showAddSheetQuestion

    End Sub

    Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
    'Boolean function assumed to be False unless set to True
    End Function


    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''

    If anyone gets other errors please post what you did to get them. Thanks again Richie for seeing that error.

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Posts
    207

    Default Re: Copy worksheet using VBA

    Hi Tom,

    I came across your code only recently and it works brilliantly, thanks.

    Just wondering, how difficult would it be to add a second field in the form to enter a date, in 'dd/mm/yyyy' format, which would be pasted to cell AG1 in the new sheet?

    Thanks

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com