Cell Value as String as Worksheet Name

D

Darrel

Currently using this macro to save copy of sheet in same book.
What am wanting to do if possible, is take the value from cell b3, a
copy is created and use it for the new sheet name. Looked at multipl
postings and haven't been able to find something specific to this. An
help would be appreciated.

Sub SaveCopy()
'
' SaveCopy Macro
'
Sheets("Summary").Select
Sheets("Summary").Copy Before:=Sheets(1)
ActiveSheet.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("Summary").Select
End Su
 
B

Bob Phillips

Hi Darrel,

Sub SaveCopy()
'
' SaveCopy Macro
'
Sheets("Summary").Copy Before:=Sheets(1)
with ActiveSheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Name = .Range("B3").Value
Enhd With
WorkSheets("Summary").Select
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Darrel

Can get it name the sheet if cell referencing is text. However, the cel
contains date imported from csv file in format of 5/3/2004
the one summary being copied has all the formulas to build a dail
summary based on input from cms.
Am thinking that maybe the actual date will need to be reassemble
somehow?
Can select cell and copy, then manually rename the sheet with pastin
it in. Would be nice if it can be done via VBA during the sheet cop
process.
Hope this clarifies, thank you for responding earlier
 
B

Bob Phillips

Hi Darrel,

How about this then

Sub SaveCopy()
'
' SaveCopy Macro
'
Sheets("Summary").Copy Before:=Sheets(1)
with ActiveSheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
.Name = Format(.Range("B3").Value, ""yyyy-mm-dd")
End With
WorkSheets("Summary").Select
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top