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 ...

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.

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.

'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

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.

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).

'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

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

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

Originally Posted by Tom Urtis

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).

'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

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