Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet naming using visual basic
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet naming using visual basic
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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual basic editor | Excel Discussion (Misc queries) | |||
Adding a dymanic formula to a cell using visual basic | Excel Discussion (Misc queries) | |||
Adding a dymanic formula to a cell using visual basic | Excel Discussion (Misc queries) | |||
Microsoft Visual Basic Error in Excel | Excel Discussion (Misc queries) | |||
Visual basic Check Box and the IF formula | Excel Discussion (Misc queries) |