Auto code to add tab

[Very Basic User]

This code works fine with one exception. I only name the new tab with a date
and time stamp. In a cell "U2" the user selects shift too. What I would like
if for the code to ready both the now section and the cell "U2" to name the
new tab. So end state I would see a tab named like this (01-01-2009 Shift 3)
is there any way to add that to this code. I hope this is enough information.

Thanks!

Sub SaveSheet()
'
' SaveSheet Macro
'

'
Sheets("DDS").Select
Sheets("DDS").Copy Before:=Sheets(3)
Sheets("DDS (2)").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Application.CutCopyMode = False
Sheets("DDS (2)").Select
Sheets("DDS (2)").Name = Format(Now, "yyyy-mm-dd_hh-mm-ss")
Range("B1").Select
Sheets("DDS").Select
Range("B1").Select
End Sub

 

[Dave Peterson]

Now will include both the time and date.

Option Explicit
Sub SaveSheet()
Dim OldWks As Worksheet
Dim NewWks As Worksheet

Set OldWks = Worksheets("DDS")

OldWks.Copy Before:=Sheets(3)

Set NewWks = ActiveSheet

With NewWks
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.Name = Format(Now, "yyyy-mm-dd_hh-mm-ss") & " " & .Range("U2").Value
End With
End Sub

You may have wanted
.Name = Format(Date, "yyyy-mm-dd") & " " & .Range("U2").Value

(and your order of mdy is different)

 

[Very Basic User]

Hello Dave this worked great, One additional question... How can I change
this line

..Name = Format(Date, "yyyy-mm-dd") & " " & .Range("U2").Value

To not take todays date, but whatever date I have entered in "U1" Sometimes
I have to change the date in U1 to update the sheet from a past date. When I
tried to just make it a .range("U1") the format included "/" marks that are
not allowed in the naming nomenclature for a tab. End state, this tab name
would be perfect. (11-Nov-09 Shift 1) with the date being pulled from
whatever date is in cell "U1" and shift being pulled from "U2"
--
Thank you for your time!
John

Now will include both the time and date.

Option Explicit
Sub SaveSheet()
Dim OldWks As Worksheet
Dim NewWks As Worksheet

Set OldWks = Worksheets("DDS")

OldWks.Copy Before:=Sheets(3)

Set NewWks = ActiveSheet

With NewWks
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.Name = Format(Now, "yyyy-mm-dd_hh-mm-ss") & " " & .Range("U2").Value
End With
End Sub

You may have wanted


(and your order of mdy is different)

 

[Dave Peterson]

..Name = Format(.range("u1").value, "yyyy-mm-dd") & " " & .Range("U2").Value

Since you can't use /'s in file names (in wintel land), the Format() stuff uses
hyphens.

Hello Dave this worked great, One additional question... How can I change
this line

.Name = Format(Date, "yyyy-mm-dd") & " " & .Range("U2").Value

To not take todays date, but whatever date I have entered in "U1" Sometimes
I have to change the date in U1 to update the sheet from a past date. When I
tried to just make it a .range("U1") the format included "/" marks that are
not allowed in the naming nomenclature for a tab. End state, this tab name
would be perfect. (11-Nov-09 Shift 1) with the date being pulled from
whatever date is in cell "U1" and shift being pulled from "U2"