I have a macro that creates reports from a main sheet. I want to make the new
sheets it creates come from a template, ie a sheet i call template in the
current workbook. This will eliminate multiple copy and paste steps in the
current macro.

I recorded a macro that right clicks the sheet tab, copies and renames but
that doesn't work when run.

any thoughts?

'The below will copy the current sheet and rename
ActiveSheet.Copy After:=ActiveSheet
Set ws = ActiveSheet
ws.Name = "newname"

'The below will copy the current sheet to the end...and rename
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
ws.Name = "newname"


If this post helps click Yes
---------------
Jacob Skaria


"thomas donino" wrote:

> I have a macro that creates reports from a main sheet. I want to make the new
> sheets it creates come from a template, ie a sheet i call template in the
> current workbook. This will eliminate multiple copy and paste steps in the
> current macro.
>
> I recorded a macro that right clicks the sheet tab, copies and renames but
> that doesn't work when run.
>
> any thoughts?

have you tried


sheets("Template").copy
activesheet.name="newnamehere"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"thomas donino" <(E-Mail Removed)> wrote in message
news:158B13C5-78D3-4CF9-832B-(E-Mail Removed)...
>I have a macro that creates reports from a main sheet. I want to make the
>new
> sheets it creates come from a template, ie a sheet i call template in the
> current workbook. This will eliminate multiple copy and paste steps in the
> current macro.
>
> I recorded a macro that right clicks the sheet tab, copies and renames but
> that doesn't work when run.
>
> any thoughts?

thank you

"Jacob Skaria" wrote:

> 'The below will copy the current sheet and rename
> ActiveSheet.Copy After:=ActiveSheet
> Set ws = ActiveSheet
> ws.Name = "newname"
>
> 'The below will copy the current sheet to the end...and rename
> ActiveSheet.Copy After:=Sheets(Sheets.Count)
> Set ws = ActiveSheet
> ws.Name = "newname"
>
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "thomas donino" wrote:
>
> > I have a macro that creates reports from a main sheet. I want to make the new
> > sheets it creates come from a template, ie a sheet i call template in the
> > current workbook. This will eliminate multiple copy and paste steps in the
> > current macro.
> >
> > I recorded a macro that right clicks the sheet tab, copies and renames but
> > that doesn't work when run.
> >
> > any thoughts?