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
|
|||
|
|||
Code Help for naming a worksheet 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 read both the Date and the cell "U2" shift 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 -- Thank you for your time! John |
#2
|
|||
|
|||
Code Help for naming a worksheet tab
Sheets("DDS (2)").Name = Format(Now, "yyyy-mm-dd") & " " & Range("U2")
OR Sheets("DDS (2)").Name = Format(Now, "yyyy-mm-dd_hh-mm-ss") & _ " " & Range("U2") If this post helps click Yes --------------- Jacob Skaria "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 read both the Date and the cell "U2" shift 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 -- Thank you for your time! John |
#3
|
|||
|
|||
Code Help for naming a worksheet tab
Thank you so much it works like a chart!
-- Thank you for your time! John "Jacob Skaria" wrote: Sheets("DDS (2)").Name = Format(Now, "yyyy-mm-dd") & " " & Range("U2") OR Sheets("DDS (2)").Name = Format(Now, "yyyy-mm-dd_hh-mm-ss") & _ " " & Range("U2") If this post helps click Yes --------------- Jacob Skaria "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 read both the Date and the cell "U2" shift 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 -- Thank you for your time! John |
#4
|
|||
|
|||
Code Help for naming a worksheet tab
Hello Jacob Skaria,
One other quick question. The top line you suggested works great, but I'm wondering if we can make a slight change. I tried the following. Sheets("DDS (2)").Name = Range("U1") & " " & Range("U2") I did this because U1 represents the date and U2 represents the shift. The only problem is that There are times that I have to change U1 to a date from the day before and when my tab is named it pulls the "Now" date versus the date I'm actually working on. So I need to format the U1 cell in a way that will be an acceptable tab name without the "/" marks etc. I don't want to change the appearance of the worksheet, but want the tab to be named as 11-Nov-09 Shift 1 format. Is there any way to update the code above to change a date format to an acceptable tab name but pull the date listed versus the "now" date? -- Thank you for your time! John "Jacob Skaria" wrote: Sheets("DDS (2)").Name = Format(Now, "yyyy-mm-dd") & " " & Range("U2") OR Sheets("DDS (2)").Name = Format(Now, "yyyy-mm-dd_hh-mm-ss") & _ " " & Range("U2") If this post helps click Yes --------------- Jacob Skaria "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 read both the Date and the cell "U2" shift 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 -- Thank you for your time! John |
Thread Tools | |
Display Modes | |
|
|