Sheet creation code modification

[Todd Huttenstine]

Below is my code... (which half way works)

I have a workbook that can contain any number of sheets.
The names of the new sheets are unknown until time of
sheet creation. The name of the new worksheet needs to be
the value that is in cell D1 of Worksheets(1).

I need the code to be modified so that it creates a new
sheet and then names that new sheet the value that is in
cell D1 of worksheet1. It must then copy worksheets(1) to
that new sheet. It will paste the formats and values only
to the new sheet.




Sub Macro3()
'
' Macro3 Macro
' Macro recorded 12/19/2003 by SEGeneric
'

Dim sha As Worksheet
Dim shar As Worksheet
Set sha = Worksheets(1)


sha.Cells.Copy

Worksheets(2).Cells.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Worksheets(2).Cells.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End Sub

Thanx

Todd

 

[Bob Phillips]

Here you are Todd,

Sub Macro3()
Dim sha As Worksheet
Dim shar As Worksheet

Set sha = Worksheets(1)
Set shar = ActiveWorkbook.Worksheets.Add

sha.Cells.Copy

shar.Cells.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

shar.Cells.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

shar.Name = sha.Range("D1").Value
End Sub


--

HTH

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

 

[Todd Huttenstine]

Thanx it works perfect.

Todd

-----Original Message-----
Here you are Todd,

Sub Macro3()
Dim sha As Worksheet
Dim shar As Worksheet

Set sha = Worksheets(1)
Set shar = ActiveWorkbook.Worksheets.Add

sha.Cells.Copy

shar.Cells.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

shar.Cells.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

shar.Name = sha.Range("D1").Value
End Sub


--

HTH

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




.