1

I am working on a code to make copies of worksheets along with its name ranges in the same workbook and then promote the scope to workbook.

Following code is used to make copies of worksheet

For A = 1 To 8 For B = 1 To 3 Set ws = Sheets(tabnames(A) & B) ws.Copy After:=Sheets(tabnames(A) & B) ActiveSheet.Name = tabnames(A) & B + 1 Next B Next A

Attached Code is used for changing name scope to workbook: https://stackoverflow.com/a/20054925

The code works fine in its first run. Once executed, when I try to rerun, the program returns an error message says:

The Name 'XX' already exists. Click yes to use that version of name, or click No to rename the version of 'XX' you are moving or copying.

The same message pops up when I copy the sheets manually. What confusing me is the same code works well when I reopen the WB. Which means after each run I have to close and open the WB to execute next run. Is there a solution for this? Perhaps something like a refresh or reset?

I tried freezing display alerts, but then I noted that the name ranges are not being copied along with the sheets.

Also, I tried a other codes mentioned in the thread above for promoting name scope and ended up the same.

Also, It works when I copy second last sheet (either manually or via the code below)

Set ws = Sheets(tabnames(A) & B-1) ws.Copy After:=Sheets(tabnames(A) & B)

Please Advise.

Improve this question
6
  • What if you delete the workbook-level name before assigning the new one? Commented Apr 5, 2020 at 16:09
  • Thanks @DougGlancy. I am trying to retain the existing names and moreover, I was trying to make the sheet automatic. Commented Apr 5, 2020 at 16:18
  • So you want the existing workbook-level name to become a worksheet-level name? Commented Apr 5, 2020 at 16:20
  • 1
    @DougGlancy Sorry if I confused you. I wanted to make copies of the existing sheets along with name ranges. Once the copies are created, I have codes to rename the names as required and assign its scope to workbook. The issue mentioned above pops up when I rerun the program to generate second sets of existing sheets. Commented Apr 5, 2020 at 16:42
  • you've run it once, you've created copies of the sheets and rename them. If you run it again and rename with the same name, how can it not error? As for what you said when you reopen the file and then can execute code, are you sure you saved it? Commented Apr 6, 2020 at 0:38

3 Answers 3

Reset to default
1

When you create the copy of the worksheet, you are also creating copies of the named ranges within the worksheet. Since they are workbook scoped names, they are duplicates, so they cannot be copied. Instead of copying the worksheet in the same workbook, copy it to a new workbook, change the names and then copy it back to the original workbook.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The error seems to go away when I make copies of the parent sheet. It seems resolved for now. But still it would be helpful if someone could help me figure out Why Icannot make a newly created sheet without a restart.

Improve this answer

Comments

0

The named range scope could be :

  • the workbook
  • or a specific scope

If the named range is the workbook you are copying some data to another workbook where there is a named range with the exact same name.

So Excel does not know which one to use : the one from the source data or destination data. That is why it asks a question.

If you agree to use the destination named range, a tip is to prevent displaying any message by adding :

Application.DisplayAlerts = False

So you code will become :

For A = 1 To 8 For B = 1 To 3 Set ws = Sheets(tabnames(A) & B) Application.DisplayAlerts = False 'Block any alert message ws.Copy After:=Sheets(tabnames(A) & B) Application.DisplayAlerts = True 'Restore any alert message ActiveSheet.Name = tabnames(A) & B + 1 Next B Next A

(in your example, you can also move the displayAlert outside of the for blocks for better speed but you take the risk to miss another important message...)

I guess this is not ideal but this is what the best I have found so far...

Good luck for you

(sorry for the English mistake because I am not English native)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.