Copying worksheets in VBA
 

Hi,

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

Sheets("Backup Listing").Copy Befo=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.

Copying worksheets in VBA
 

Try this

Sub test()
Sheets("Backup Listing").Copy Befo=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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"GLT" wrote in message ...
Hi,

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

Sheets("Backup Listing").Copy Befo=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.

Copying worksheets in VBA
 

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 Befo=Sheets(1)
ActiveSheet.Name = "Sheet2"

End Sub

Ron de Bruin wrote:

Try this

Sub test()
Sheets("Backup Listing").Copy Befo=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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"GLT" wrote in message ...
Hi,

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

Sheets("Backup Listing").Copy Befo=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.

--

Dave Peterson

Copying worksheets in VBA
 

Yes this is better Dave

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dave Peterson" wrote in message ...
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 Befo=Sheets(1)
ActiveSheet.Name = "Sheet2"

End Sub

Ron de Bruin wrote:

Try this

Sub test()
Sheets("Backup Listing").Copy Befo=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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"GLT" wrote in message ...
Hi,

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

Sheets("Backup Listing").Copy Befo=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.

--

Dave Peterson