+ Reply to Thread
Results 1 to 2 of 2

renaming macro fail

  1. #1
    Registered User
    Join Date
    09-18-2009
    Location
    england
    MS-Off Ver
    Excel 2003
    Posts
    24

    Smile renaming macro fail

    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

  2. #2
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Dorset, UK
    MS-Off Ver
    Office 2003, Office 2007
    Posts
    131

    Re: renaming macro fail

    Firstly, this should probably be on the Excel Programming board rather than Worksheet Functions...

    Secondly, you can't have two tabs with the same name, so if you just click to run this code again you'll end up trying to duplicate sheet names, which will give you an error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1