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
|