Excel 2003 - rename worksheet using date and time 1

[smooveb]

Hi,

I'm very close to being able to insert a copy of one worksheet at the end of my workbook, and rename the new sheet using current date and time. Date is easy, it's time that's giving me a little hassle - presumably since Excel doesn't like you using : in your sheetname. Here's my code:

Dim curdate As String
curdate = Format(Date, "mm-dd-yy hh:mm:ss")
Sheets("Summary").Select
ActiveSheet.Cells.Select
Selection.Copy
Dim nLast As Long
Dim i As Long

With ActiveWorkbook
nLast = .Sheets.Count
For i = 1 To ActiveWindow.SelectedSheets.Count
.Sheets.Add _
After:=.Sheets(.Sheets.Count), _
Type:="worksheet", _
Count:=1
Next i
.Sheets(nLast + 1).Select
ActiveCell.Cells.Select
ActiveSheet.Paste
ActiveSheet.Name = curdate

End With

Only the activesheet.name = curdate is giving me hassles. Any recommendations how to scrub the colons before this line?

Thanks!
Barrett

 

[SkipVought]

If you would have tried renaming a sheet tab MANUALLY, you would have observed that the COLON is not accepted as a valid character in a sheet name.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[smooveb]

Sorry, I thought my post indicated that I had tried this, and appreciate the need to scrub the colons from curdate before trying to name the sheet with it.

To clear up the question, I'm trying to set curdate to reflect date and time, but without colons.

Thanks!
Barrett

 

[SkipVought]

using what code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

why not

curdate = Format(Date, "mm-dd-yy hhmmss")

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[smooveb]

Hi,

perhaps I'm still not being clear.

I'm using the code:

curdate = Format(Date, "mm-dd-yy hh:mm:ss")

to set curdate. I need to use alternative code to set curdate to include month, day, year, hours, minutes and seconds, but without the colons (as we've discussed, a worksheet cannot be named using colons).

I'm here to ask for ideas for alternate code to use that will meet my needs.

Cheers,
Barrett

 

[SkipVought]

Check my previous post.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[smooveb]

Yup, that worked. Part of what I was doing wrong was setting only using date, but not time as well. There may be a better way of doing this, but it worked. Thanks!


Dim curdate As String
curdate = Format(Date, "mmmddyy") & Format(Time, "hhmmss")
Sheets("Summary").Select
ActiveSheet.Cells.Select
Selection.Copy
Dim nLast As Long
Dim i As Long

With ActiveWorkbook
nLast = .Sheets.Count
For i = 1 To ActiveWindow.SelectedSheets.Count
.Sheets.Add _
After:=.Sheets(.Sheets.Count), _
Type:="worksheet", _
Count:=1
Next i
.Sheets(nLast + 1).Select
ActiveCell.Cells.Select
ActiveSheet.Paste
ActiveSheet.Name = curdate

End With



End Sub

 

[SkipVought]

consider...

    Dim nLast As Long
    Dim i As Long
    
    Sheets("Summary").Cells.Copy
        
    With ActiveWorkbook
        nLast = .Sheets.Count
        For i = 1 To ActiveWindow.SelectedSheets.Count
            .Sheets.Add _
                    After:=.Sheets(.Sheets.Count), _
                    Type:="worksheet", _
                    Count:=1
        Next i
        With .Sheets(nLast + 1)
            .[A1].Paste
            .Name = [b]Format(Now, "mmmddyy hhmmss")[/b]
        End With
    End With

No need to create a variable for curdate, as you only use it one time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

No need to create a variable for curdate, as you only use it one time. "

yeah hurray!

Gerry

 

[Loomah]

just curious about the selecting and copying and looping etc. would this work as well?

Sub mit()
Worksheets("Sheet1").Copy after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Format(Now, "yymmdd hhmmss")
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Yes, this was a curiosity to me. You have this loop going on, adding sheets, and then ONLY the last sheet is named. Shouldn't the naming be going on INSIDE the loop?

    With ActiveWorkbook
        For i = 1 To ActiveWindow.SelectedSheets.Count
            .Sheets.Add _
                    After:=.Sheets(.Sheets.Count), _
                    Type:="worksheet", _
                    Count:=1[b]
            With .Sheets(.Sheets.Count)
                .[A1].Paste
                .Name = Format(Now, "mmmddyy hhmmss") & i
            End With[/b]
        Next i
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]