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

Sub AddAndNameNewSheet()
    Dim wsNew As Worksheet
    
    Set wsNew = Sheets.Add
    wsNew.Name = "YourSheetName"
    Set wsNew = Nothing
End Sub

[DonkeyOte]

or perhaps:

Sheets.Add.Name = "Consolidation Assumptions"

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.

Option Explicit

Sub AddNewSheets()
    AddNewSheet "Consolidation Assumptions"
    AddNewSheet "abc"
    AddNewSheet "xyz"
    ' etc, etc......
End Sub

Sub AddNewSheet(strSheetName As String)
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = strSheetName Then
            MsgBox "A Sheet Named" & vbCrLf & vbCrLf _
                    & Chr(34) & strSheetName & Chr(34) & vbCrLf & vbCrLf _
                    & "Already Exists", _
                    vbOKOnly + vbCritical, _
                    "Duplicate Sheet Name Found."
            Set ws = Nothing
            Exit Sub
        End If
    Next
        
    Sheets.Add.Name = strSheetName
    Sheets(strSheetName).Move After:=Sheets(Sheets.Count)
    Set ws = Nothing
End Sub

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

Cheers