Copy and rename tabs

[fatpiggy123]

Hi,

I'm trying to create a macro to insert a new tab and rename it to "Consolidation Assumptions".

However it keeps failing as the sheet thats being inserted may change its existing name, ie - it may be "sheet 9" then next time i run the macro it may be "sheet 10" depending on what i've done before.

Does any one know the code i should use?

Any help appreciated,

Thanks

[fatpiggy123]

This is my code...error occurs at "activesheet.select.name" = line

Sub Consolidation()
'
' Consolidation Macro
'

'
Sheets("Assumptions").Select
Sheets.Add
ActiveSheet.Select
ActiveSheet.Select.Name = "Consolidation Assumptions"
Range("A1").Select
End Sub

[Marcol]

Try

Please Login or Register  to view this content.

[DonkeyOte]

or perhaps:

Please Login or Register  to view this content.

remember that

a) you can only have 1 sheet of the same name (case irrelevant) - trying to name a sheet with the same of an existing sheet will throw an error (ie you might want to add a handler)

b) when a new sheet is added it is immediately the active sheet

[Marcol]

Good point Don,

I'm going to have to stop rushing, a bit of thought would save me time in the end.

Cheers

[Marcol]

This might be useful if you need to add more than one sheet.

Please Login or Register  to view this content.

Try it In a new workbook, it will only add the named sheets once.

Cheers