vba 将命名范围从一个工作簿复制粘贴到另一个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12210712/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy Paste a named Range from one workbook to another
提问by Him
I want to copy a named range from a template to my current workbook, which is blank. The problem is that I don't have any existing range inside this new workbook. So, I am not able to copy paste the range from other book. This is what I have tried but doesn't seem to be working:
我想将模板中的命名范围复制到我当前的空白工作簿。问题是我在这个新工作簿中没有任何现有范围。所以，我无法从其他书中复制粘贴范围。这是我尝试过但似乎不起作用的方法：
Sub cpyRange()
Dim rng1 As Range, rng2 As Range
Set rng2 = Workbooks.Open("C:\SVN\Template.xls").Sheets("Report").Range("REPORT")
rng2.Copy
ThisWorkbook.Sheets(1).Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
MsgBox ("done")
End Sub
I want to copy paste all the data inside that range to my current workbook along with it's range name.
我想将该范围内的所有数据及其范围名称复制粘贴到我当前的工作簿中。
回答by Doug Glancy
You have at least two specific issues in your code: 1. PasteSpecial is a method of a Range not a worksheet. 2. Pasting doesn't copy the range name.
您的代码中至少有两个特定问题： 1. PasteSpecial 是 Range 的一种方法，而不是工作表。2. 粘贴不会复制范围名称。
The code below does the copy, to cell A1 of the target sheet, and then creates a Name that refers to the selection in the target sheet:
下面的代码将复制到目标工作表的单元格 A1，然后创建一个名称来引用目标工作表中的选择：
Sub cpyRange()
Dim SourceWorkbook As Excel.Workbook
Dim RangeName As String
Dim TargetWorkbook As Excel.Workbook
Dim TargetSheet As Excel.Worksheet
RangeName = "REPORT"
Set SourceWorkbook = Workbooks.Open("C:\SVN\Template.xls")
SourceWorkbook.Sheets("Report").Range(RangeName).Copy
Set TargetWorkbook = ThisWorkbook
Set TargetSheet = TargetWorkbook.Sheets(1)
TargetSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
TargetWorkbook.Names.Add RangeName, "='" & TargetSheet.Name & "'!" & Selection.Address
SourceWorkbook.Close savechanges:=True
MsgBox ("done")
End Sub
I added a few more variables, to try to make it a little more flexible.
我添加了更多变量，以使其更加灵活。
