new sheet created 'on the fly' from template in same workbook - H.

M

MrT

looking for an excel solution to create a new sheet at the end based on the
contents of a template sheet within the same workbook.
 
D

Dave Peterson

One way:

Worksheets("template").Copy _
after:=Worksheets(Worksheets.Count)
 
M

MrT

Thankyou Dave

Could you help with another question?

I am very new to excel and am trying to build a tool to help me define tasks
in my work day schedule.

Now I can create a new sheet 'on the fly' How would I copy the contents of a
cell in an existing sheet to a cell on the new sheet; and How would i rename
the new sheet based on cell contents?

MrT
 
D

Dave Peterson

It's probably best to just start a new thread for different questions. And I
think it's better to address any question to the whole world--not just one
person. You'll usually get a quicker response than waiting for any particular
person.

But this worked ok for me:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim TemplateWks As Worksheet
Dim OtherWks As Worksheet

Set TemplateWks = Worksheets("template")
Set OtherWks = Worksheets("othersheet")

TemplateWks.Copy _
after:=Worksheets(Worksheets.Count)

'the new sheet is now the activesheet
Set newWks = ActiveSheet

With newWks
OtherWks.Range("a1").Copy _
Destination:=.Range("a1")
On Error Resume Next
.Name = .Range("a1").Value
If Err.Number <> 0 Then
'rename didn't work
MsgBox "Please rename: " & .Name & " manually"
Err.Clear
End If
On Error GoTo 0
End With

End Sub

(I copied and pasted from A1 to A1--but you can change either/both address.)
 
Top