Excel VBA Error "That name is already taken"

Topic

New Reply

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).

 

Reply | Quote

Replies

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

Reply | Quote

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)

Reply | Quote

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

 

Reply | Quote

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

Reply | Quote

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

Reply | Quote

RetiredGeek wrote:

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.

RetiredGeek

Reply | Quote