If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|