Excel VBA:多个命名区域的复制循环

Question

我想复制几个命名范围。这些范围有一个集合的命名约定，先是短字符串，然后是数字(例如，Body1、Body2 等(。

我的解决方法循环遍历每个工作表名称，并复制范围 B2：C50 而不是每个命名范围，这是我更喜欢的。

法典：

Dim Bnum As Integer          'Bnum is Loop Counter
Dim Nbodies As Integer       'Nbodies is how many loops should go
Dim BodyNum As String
'BodyNum is the sheet name where the named range is (and also the named range I`d like to copy). The named ranges are workbook wide in this case

Nbodies = Range("N.Bodies")  'Called from elsewhere in the workbook
Bnum = 1                    'Initial counter set
BodyNum = "Body" & Bnum     'Name of worksheet+named range which I want to copy
Do Until Bnum = Nbodies + 1
    Workbooks(Wkbk).Activate
    Worksheets(BodyNum).Range("B2:C50").Copy
'Copies range manually definied in vba B2:C50 in this case. Want to copy named range with the same name as Bodynum, varying the digit for each loop
    Workbooks(Inputbk).Activate     'The paste portion works fine
    Selection.PasteSpecial Paste:=xlPasteValues
    Call Cursor.Cursor
    Workbooks(Wkbk).Activate
    Bnum = Bnum + 1         'Iterates loop counter
    BodyNum = "Body" & Bnum
Loop

Answer 1

我的理解是你有一组名为"Body1"的工作表。BodyN"，每个区域都有一个带有工作表本身名称的命名区域。

你

提到你Want to copy named range with the same name as Bodynum.

那么你所要做的就是扭转这条线：

Worksheets(BodyNum).Range("B2:C50").Copy

进入这个：

Worksheets(BodyNum).Range(BodyNum).Copy