Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Worksheets | Excel Discussion (Misc queries) | |||
Copying between Worksheets | Excel Worksheet Functions | |||
Copying Worksheets | Excel Worksheet Functions | |||
copying worksheets into one | Excel Worksheet Functions | |||
Copying worksheets | Excel Worksheet Functions |