VB / Excel Problem (resolved)

[Blake]

Ok, so I’m pretty new to VB, and this will probably be very obvious – but still, I don’t know, so I need to ask.

Ok, so I have this worksheet called TemplateSheet, and I need to create a macro that will make a copy of that worksheet, and call it CurrentOrder.

That is no problem. The problem is that if a worksheet called CurrentOrder already exists, then it has to be deleted. I have no idea how to say “If CurrentOrder exists then…”

 

[Blake]

I’ve had a bit of a further look at it, and I have something that probably should work, but doesn't.

Sub auto_open()

    Dim sheetExists As Boolean
    Public CurrentOrder As Sheets
    
    sheetExists = False
    CurrentOrder = New Sheets
    
End Sub

Private Sub CommandButton1_Click()

    If sheetExists = False Then
        Sheets("TemplateSheet").Copy Before:=Sheets(1)
        Sheets("TemplateSheet (2)").Select
        Sheets("TemplateSheet (2)").Name = "CurrentOrder"
        sheetExists = True
    Else
        Sheets("CurrentOrder").Delete
        Sheets("TemplateSheet").Copy Before:=Sheets(1)
        Sheets("TemplateSheet (2)").Select
        Sheets("TemplateSheet (2)").Name = "CurrentOrder"
    End If
    
End Sub

 

[Carl.]

There are a two errors in your code:

• "Sheets" is a collection of worksheets, and used alone it returns the current workbook's sheets collection. If you want to use a variable for a worksheet, it should look like: "Dim s as Worksheet"
• If the user deletes or rename manually the worksheet, the variable "sheetExits" will remain unchanged. It's better to set up a test inside your sub to check if the worksheet exists.

This should work:

Private Sub CommandButton1_Click()
    Dim s As Worksheet
    
    For Each s In Sheets
        If s.Name = "CurrentOrder" Then
            Application.DisplayAlerts = False
            s.Delete
            Application.DisplayAlerts = True
            Exit For
        End If
    Next s

    Sheets("TemplateSheet").Copy Before:=Sheets(1)
    Sheets(1).Name = "CurrentOrder"
    'Remove the next line if this button isn't placed on "TemplateSheet"
    Sheets(1).CommandButton1.Visible = False
End Sub

Since there's no method to "search" or "find" worksheets, you need to iterate through the collection "Sheets" using the keywords "For Each", which are quite handy in vb/vba. Another way to do it would have been to directly try to delete the sheet "CurrentOrder", and handle the error raised when it doesn't exist, but the method above is more straightforward.

If you don't set "Application.DisplayAlerts", it'll still work, but It will prompt the user before deleting the worksheet. It's obviously safer to put it back on (true) once it's done, to avoid closing Excel without saving changes, for example.

The line "Sheets(1).CommandButton1.Visible = False" is to avoid the button from being used from the new worksheet. This would cause an error, since the code of the button would be trying to delete its own worksheet. Remove this line of code if your button is placed on a userform or any other worksheet than "TemplateSheet".

You don't require anything else ouside this Sub, so you can delete the auto_open sub.

Hope that helps.