将所有命名的范围复制到另一个工作簿 [英] Copy all named ranges to another workbook

问题描述

因此，我一直在对Microsoft支持人员对此问题的回答大惊小怪，但他们的代码根本无法正常工作.我对它们如何不定义x感到困惑，或者x始终等于VBA中的变量?无论如何，我试图将 all 命名范围复制到一个工作簿中，并将它们带到另一工作簿中.我必须对50多个工作簿执行此操作，并且基本工作簿中有30-40个命名范围.我真的不想手动复制粘贴这些bugger.这是他们为解决此问题而提供的代码:

  Sub Copy_All_Defined_Names()'遍历活动目录中所有已定义的名称的工作簿.对于ActiveWorkbook.Names中的每个x'将活动工作簿中的每个已定义名称添加到目标工作簿("Book2.xls"或"Book2.xlsm").'"x.value"是指引用该单元格的单元格'定义的名称指向.Workbooks("trial run.xlsm").Names.Add Name:= x.Name，_RefersTo:= x.Value下一个x结束子 

运行此命令会给我以下错误:

并突出显示以下代码:

"trial run.xlsm"只是一个空白工作簿，并保存为启用宏的命令；这是我的目标工作簿.似乎很无聊，不得不寻求帮助来调试Microsoft编写的宏，但是我有点儿不高兴了.

对于我从中获得的原始页面，请参见此处:

解决方案

很难告诉您，但我怀疑您的RefersTo字符串是> 256个字符:如果这样，它将失败，因为VBA无法处理> 256个字符的参照字符串.

So I've been fussing with the Microsoft Support answer to this question but their code is simply not working. I'm confused about how they do not define x, or maybe x always equals a variable in VBA? Anyway, I am trying to copy all named ranges in one workbook and bring them over to another workbook. I have to do this for 50-odd workbooks and there are 30-40 named ranges in the base workbook. I really do not want to have to copy-paste these buggers manually. Here's the code they gave for this problem:

Sub Copy_All_Defined_Names()
   ' Loop through all of the defined names in the active
   ' workbook.
     For Each x In ActiveWorkbook.Names
      ' Add each defined name from the active workbook to
      ' the target workbook ("Book2.xls" or "Book2.xlsm").
      ' "x.value" refers to the cell references the
      ' defined name points to.
      Workbooks("trial run.xlsm").Names.Add Name:=x.Name, _
         RefersTo:=x.Value
   Next x
End Sub

Running this gives me the following error:

and highlights the following code:

"trial run.xlsm" is just a blank workbook and is saved as macro-enabled; this is my target workbook. Seems silly to have to ask for help debugging a macro that microsoft wrote, but I'm kinda at the end of my rope here.

For the original page I got this from, see here: Microsoft Support

This is what my named ranges look like:

解决方案

Hard to tell but I suspect that your RefersTo string is >256 characters: If so it will fail because VBA cannot handle refersto strings>256 characters.

这篇关于将所有命名的范围复制到另一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！