Hi all,
I am trying to create a macro that copies selected tabs and renames them depending on cell contents.
I have the following code that works the first time i run it. However if i try and run it again and try to create a third set of tabs it fails.
The macro currently allows the first set of tabs to be renamed depending on the contents of cell D15 and the new tabs on the contents of D16
Any ideas. Thanks
Option Explicit
Sub NewCompany()
Dim MyArray As Variant
Dim i As Long
Dim strName1 As String
Dim strName2 As String
MyArray = Array("Assumptions", "Yearly", "Monthly", "Sales Calc", _
"Sales and COS", "FAs 1", "Loan", "HP 1")
strName1 = Sheets("Overview").Range("D15")
strName2 = Sheets("Overview").Range("D16")
For i = 0 To UBound(MyArray)
Sheets(MyArray(i)).Copy After:=Sheets(4 + i)
ActiveSheet.Name = MyArray(i) & " " & strName2
Worksheets(MyArray(i)).Name = MyArray(i) & " " & strName1
Next i
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Application.DisplayAlerts = False
Sheets("TOC").Delete
Application.DisplayAlerts = True
Application.Run "'" & ActiveWorkbook.Name & "'!CreateTOC"
End Sub
Bookmarks