Auto code to add tab
 

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 Befo=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

--
Thank you for your time!
John

Auto code to add tab
 

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 Befo=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 wrote:

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 Befo=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

--
Thank you for your time!
John

--

Dave Peterson

Auto code to add tab
 

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


"Dave Peterson" wrote:

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 Befo=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)



Very Basic User wrote:

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 Befo=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

--
Thank you for your time!
John

--

Dave Peterson
.

Auto code to add tab
 

..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.



Very Basic User wrote:

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

"Dave Peterson" wrote:

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 Befo=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)



Very Basic User wrote:

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 Befo=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

--
Thank you for your time!
John

--

Dave Peterson
.


--

Dave Peterson