VBA Copy Template Sheet & Rename from Cell

[mikemmb]

Hi Guys,
I need help please dealing with errors when creating new sheets from a template.

Objective is:
Within a sheet named CUSTOMERS:
1. Enter an Invoice Number in Cell G3.
2. Click a Forms Button to run Sub "CreateInvoice".

Sub "CreateInvoice" should:
1. Copy a TEMPLATE sheet and place this copy after the CUSTOMERS sheet.
2. Name the new copy Sheet Tab with the contents of CUSTOMERS sheet cell G3.
3. Clear the contents of CUSTOMERS sheet cell G3.
4. Display the newly created sheet ready to use (presume by placing cursor in cell X of the new sheet?).

Plus a nice to have would be to react to an "Error" in a meaningful way.
By that I mean do not create duplicate sheet names (ie: "xxxxx(2)") but bring up a prompt to warn the user.

The following code is basically working in that it creates a new sheet with the cell G3 name.
But the whole error trapping bit is in its n'th revision and I am going round in ever decreasing circles!

"Sub CreateInvoice_Click()
'Copies the TEMPLATE sheet and names it with the Invoice Number in cell G3 of CUSTOMERS sheet

Dim MySheetName As String

Sheets("TEMPLATE").Copy After:=Sheets("CUSTOMERS")

ActiveSheet.Name = Sheets("CUSTOMERS").Range("G3")

On Error GoTo Err_Trap
ActiveSheet.Name = MySheetName

Err_Trap:
If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("TEMPLATE(2)").Delete
Application.DisplayAlerts = True
MsgBox ("NEW INVOICE NOT CREATED." & Chr(10) & _
"Invoice Already Exists."), vbInformation, "Sheet Creation Error"
Exit Sub
End If
End Sub"

Any help would be much appreciated.

Regards,
Mike

 

[HackSlash]

Avoid using the VBA error handler, when possible. Instead, test for the condition that causes the error before the exception.

It sounds like you are trying to avoid creating a sheet that already exists. So make a sub like this:

<code>Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each sheet In Worksheets
        If sheetToFind = sheet.name Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
End Function</code>

And use it like so:

If sheetExists("TEMPLATE") Then
   MsgBox ("NEW INVOICE NOT CREATED." & Chr(10) & "Invoice Already Exists."), vbInformation, "Sheet Creation Error"
Else
   Sheets("TEMPLATE").Copy After:=Sheets("CUSTOMERS")
End If

 

[mikemmb]

Hi,
This is a repost , thanks for your earlier reply.

I like the idea of avoiding falling into the hole rather than trying to figure out how to get out!

Basically yes I am trying to avoid creating a sheet that already exists, however I have two things I am not sure of in your reply.

1. I really don't understand what to do with the Code: "If sheetToFind" = sheet.name".
Can you please explain how I would implement this in practice?

2. If someone manually created a copy of my "TEMPLATE" sheet, this would be automatically named as "TEMPLATE(2)".
How would your code react to what is a sheet that already exists....but the sheet name isn't identical?

Thanks & Regards
Mike

 

[HackSlash]

The "Function sheetExists" is complete. You don't need to edit it unless you want to change it's behavior, which we might. You pass it the name of the sheet you are checking and it returns true or false. That's why I gave an example of how to use it in the second code block.

As it is, a partial match would come up FALSE. You could modify the "sheetExists" function to perform a partial match if you would like that behavior better.

"sheetToFind" contains the value passed in to "Function sheetExists" as you can see it in the parenthesis.

sheet.name is the name of each sheet in the workbook, one by one.

So if you want a partial match you need to change the IF statement to a partial match function like this:

If InStr(Sheet.Name, sheetToFind) Then

This says "If you find the string from sheetToFind inside the Sheet.name then this logic is true"

I hope that makes sense

 

[mikemmb]

Great,
all is clearer, will get back onto implementing it in a day or so and report back.

Thanks again
Mike