Copying worksheets in VBA

[GLT]

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


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


"GLT" <[email protected]> wrote in message news:[email protected]...
> 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.

[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 wrote:
>
> 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
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
> "GLT" <[email protected]> wrote in message news:[email protected]...
> > 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.

--

Dave Peterson

[Ron de Bruin]

Yes this is better Dave

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


"Dave Peterson" <[email protected]> wrote in message news:[email protected]...
> 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 wrote:
>>
>> 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
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>> "GLT" <[email protected]> wrote in message news:[email protected]...
>> > 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.
>
> --
>
> Dave Peterson