News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Excel VBA Error "That name is already taken"

    Posted on lmandel Comment on the AskWoody Lounge

    Home Forums AskWoody support Microsoft Office by version Excel VBA Error "That name is already taken"

    This topic contains 6 replies, has 3 voices, and was last updated by  Kirsty 1 year, 5 months ago.

    • Author
      Posts
    • #170566 Reply

      lmandel
      AskWoody Lounger

      I have an Excel VBA program using the following line to prepare to copy data from one worksheet to another:
      ActiveSheet.Name = “Med1S”
      When executed, this line generates error 1004 – “That name is already taken. Try a different one.”

      I understand that this error is generated when you try to create/rename a tab to an existing name, not assign the active sheet to an existing tab (see below).

      med1s

       

      Attachments:
    • #170589 Reply

      RetiredGeek
      AskWoody MVP

      lmandel,

      You can not copy sheet by assignment.

      To copy a sheet.

      ‘***First make sure there is not a sheet by the name you want to use.

      On Error GoTo CopySheet
      Sheets(“Med1S”).Activate
      Sheets(“Med1S”).Delete  ‘*** If sheet exists delete it!
      On Error GoTo 0

      CopySheet:
      Sheets(“Report – Unit”).Select
      Sheets(“Report – Unit”).Copy Before:=Sheets(“Med2S”)  ‘*** Copy before Med2S
      Sheets(“Report – Unit (2)”).Name = “Med1S”                     ‘*** Rename the copy

      HTH

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #170611 Reply

        lmandel
        AskWoody Lounger

        Thanks – I was not clear in my description. We are just using the statement

        ActiveSheet.Name = “Med1S”

        to make the sheet “Med1S” active so we can move data to it. I’ll change the syntax and see how it works!

        Thanks again!!

        EDIT html to text (if using Word to copy, save as .txt before pasting, to avoid that)

      • #170706 Reply

        lmandel
        AskWoody Lounger

        I’m getting the same error when replacing

        ActiveSheet.Name = “Med1S”

        with

        Sheets(“Med1S”).Activate

        This is the larger context of the code. I appreciate your help – any other ideas?

        ============================

        SkipMeas = False

        ActiveWorkbook.Worksheets(“Report – Service”).Select

        SvcName = ActiveSheet.Range(“rngSNServ”)

        Range(“F11”).Select

        If ActiveCell.Offset(0, -2).Value > 0 Then

        Selection.ShowDetail = True

        ‘Set names

        Sheets(“Med1S”).Activate

        TableName = ActiveSheet.ListObjects(1).Name

        If IsEmpty(Range(“A2”)) Then BlankCopy = 1 Else BlankCopy = 0

        Columns(“AY:AY”).Select

        Selection.Cut

        Columns(“K:K”).Select

        Selection.Insert shift:=xlToRight

        Sheets(“Med1S”).ListObjects(TableName).Range.Columns(“G:K”).Select

        Edit to remove HTML

         

    • #170744 Reply

      RetiredGeek
      AskWoody MVP

      lmandel,

      Could you possibly attach a sanitized test workbook so we can use debugging techniques on our machines?

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #170821 Reply

        lmandel
        AskWoody Lounger

        Thank you very much for your offer. Let me see what we can do. Issue is that data populating this report is filled with confidential patient information (we are a hospital). Will see what we can do.

        Larry

      • #170840 Reply

        Kirsty
        Da Boss

        Could you possibly attach a sanitized test workbook

        Only graphics files can be uploaded here. For long text, use pastebin.com, and paste a link to a Cloud drive, for a file.

        1 user thanked author for this post.

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Excel VBA Error "That name is already taken"

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.