Dynamic ranges VBA

Question

I have two workbooks "Source" and "Target". I want to go through each sheet of Target workbook and find all the named ranges. If the range name is the same with the name of the sheet (from the other workbook: Source) then I will copy the data from Source to Target.

Let's say in Source I have a sheet ("Test1") and in Target I have a named range ("Test1") in a random sheet. I want to match those and copy data from sheet "Test1" to Range "Test1".

But I can't figure out how to refer to a dynamic range/array.. I would like to use array because it is faster. Any ideas please? Thanks is advance. This is what I have tried so far:

Public Sub test()    
Dim wb1 As Workbook, wb2 As Workbook
    
Set wb1 = Workbooks("Source.xlsx")
Set wb2 = Workbooks("Target.xlsx")

For Each ws2 In wb2.Worksheets
    For Each nm In wb2.Names
        For Each ws1 In wb1.Worksheets
          If ws1.Name = nm.Name Then
          'Here is the problem, I don't know how to define this dynamic array properly:
          'It should be arrTrgt3 = ws2.Range("A1:D5") but I want to use named range, and I don't 
          'know this name yet.
            arrTrgt3 = nm.RefersToRange.Address            
               ws2.Range("A1").Resize(UBound(arrTrgt3), UBound(arrTrgt3, 2)) = arrTrgt3
          End If
        Next
     Next
Next
End Sub

Toddleson · Accepted Answer · 2021-07-26 21:49:15Z

1

In your code nm is a name object from the collection Workbook.Names.

nm.RefersToRange is the Range object that it is connected with.

nm.RefersToRange.Value is a 2D array of all the cell values from that range.

Change arrTrgt3 = nm.RefersToRange.Address into arrTrgt3 = nm.RefersToRange.Value and now arrTrgt3 is a 2D array containing cell values from the named range.

Notes: If the named range is a single cell, the returned value from Range.Value is a single variant instead of an array. This will cause an error with the Ubound() function. You may want to add an if/else to avoid that error.

answered Jul 26, 2021 at 21:49

Toddleson

4,4852 gold badges9 silver badges30 bronze badges

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

3 Comments

TedroyG Over a year ago

Actually, I want to paste the copied values in that named range..so instead of Range("A1") it should be the named range (which can start from B5 for example). Do you know how can I do this? Thanks in advance :)

Toddleson Over a year ago

I'm not sure I understand the difference but try ws2.Range(nm.RefersToRange.Address).Resize(UBound(arrTrgt3), UBound(arrTrgt3, 2)) = arrTrgt3. This will put the values of the named range, from its sheet, into the ws2 sheet, in the same location in the new sheet as it was in the old sheet.

TedroyG Over a year ago

I am asking for this because I have multiple named ranges on the same sheet and their position may differ. Probably it still doesn't make much sense to you.. Thank you tho, you helped me a lot!

Collectives™ on Stack Overflow

Dynamic ranges VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related