Copying worksheets in VBA

[Guest]

Hi,

I used a macro to copy a worksheet and the code that resulted was the
following:

Sheets("Backup Listing").Copy Before:=Sheets(1)

The result is a worksheet with the same name but the number 2 in brackets.

Does anyone know if I can force this worksheet to have a name (say SHEET2)
in the command above - rather than Excel allocating a name?

Also, when I open the workbook, I need to delete any copies of (SHEET2) that
may exist.

If anyone could provide any assistance I would be most greatful.

Cheers,
GLT.

 

[Ron de Bruin]

Try this

Sub test()
Sheets("Backup Listing").Copy Before:=Sheets(1)
On Error Resume Next
ActiveSheet.Name = "Sheet2"
If Err.Number > 0 Then
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
ActiveSheet.Name = "Sheet2"
Application.DisplayAlerts = True
Err.Clear
Else
ActiveSheet.Name = "Sheet2"
End If
On Error GoTo 0
End Sub

 

[Dave Peterson]

Why not just delete it to start:

Sub test()

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Application.DisplayAlerts = True
On error goto 0

Sheets("Backup Listing").Copy Before:=Sheets(1)
ActiveSheet.Name = "Sheet2"

End Sub

 

[Ron de Bruin]

Yes this is better Dave