Worksheet naming using visual basic

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

O'C - 31 Oct 2006 08:00 GMT

What I have is a work schedule based on a calendar setup. I currently use a
macro to automatically save the "schedule" worksheet by copying the worksheet
to a new sheet which renames it using the date from cell A2, i.e. "26 Oct -
15 Nov 2006".

The code looks like this:

Sheets("SCHEDULE").Copy AFTER:=Worksheets(Worksheets.Count)

Sheets("SCHEDULE (2)").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("a1").Select

Sheets("SCHEDULE (2)").Name = Sheets("SCHEDULE (2)").Range("$a$2")

Sheets("INFO").Select
Calculate

Sheets("SCHEDULE").Select
Range("A1").Select

The problem I have is that when I make a mistake and/or correction and try
to save it again I get the old run time error 1004 saying that you can't
rename a sheet when I have that same existing name from the previous save.
When I stop the debugger it still copies it to "schedule (2)".

Here's what I want. I want it to either overwrite the same named file with
the new one or automatically name it with the date and the (2) symbol and so
on. I don't want the debugger to be tripped by an error.

I appreciate any help you can give me...

Reply to this Message

Bob Phillips - 31 Oct 2006 10:52 GMT

Dim ws As Worksheet
Dim i As Long
Dim wsTemp As Worksheet
Dim sName As String

Sheets("SCHEDULE").Copy AFTER:=Worksheets(Worksheets.Count)
Set ws = ActiveSheet
With ws
.UsedRange.Value = .UsedRange.Value
.Range("A1").Select

i = 1
sName = Sheets("SCHEDULE (2)").Range("$A$2").Value
On Error Resume Next
Do
Set wsTemp = Nothing
Set wsTemp = Worksheets(sName & IIf(i = 1, "", "(" & i & ")"))
If wsTemp Is Nothing Then
.Name = sName & IIf(i = 1, "", "(" & i & ")")
Else
i = i + 1
End If
Loop Until wsTemp Is Nothing
On Error GoTo 0
End With

Sheets("INFO").Calculate

Sheets("SCHEDULE").Select
Range("A1").Select

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> What I have is a work schedule based on a calendar setup. I currently use a
> macro to automatically save the "schedule" worksheet by copying the worksheet
[quoted text clipped - 32 lines]
>
> I appreciate any help you can give me...

Reply to this Message

O'C - 31 Oct 2006 18:30 GMT

Thanks Bob, it works like a champ. I wish I knew as much about visual basic.
It looks like you can do anything with it.

> Dim ws As Worksheet
> Dim i As Long
[quoted text clipped - 68 lines]
> >
> > I appreciate any help you can give me...

Reply to this Message