Worksheet naming using visual basic
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.
"Bob Phillips" wrote:
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
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"O'C" wrote in message
...
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...
|