Copy worksheet using VBA

rbergeron

New Member
Joined
May 25, 2003
Messages
1
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
WELCOME TO THE BOARD!

Is this what you are looking for?

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> Copy_Sheet()
<SPAN style="color:darkblue">Dim</SPAN> NewName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> Sh <SPAN style="color:darkblue">As</SPAN> Worksheet
    NewName = ActiveSheet.Name
DuplicateSearch:
    <SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> Sh <SPAN style="color:darkblue">In</SPAN> Workbooks("Book2.xls").Worksheets
        <SPAN style="color:darkblue">If</SPAN> UCase(Sh.Name) = UCase(NewName) <SPAN style="color:darkblue">Then</SPAN>
            NewName = InputBox(NewName & " already exists.  Please enter a new name.", "Enter <SPAN style="color:darkblue">New</SPAN> Name")
            <SPAN style="color:darkblue">GoTo</SPAN> DuplicateSearch
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">Next</SPAN> Sh
    ActiveSheet.Copy Workbooks("Book2.xls").Worksheets(1)
    ActiveSheet.Name = NewName
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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

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

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top