Set the range based on a cell input which a named range

Question

Bit of newbie to VBA. I am trying to copy values from a source spreadsheet to a destination spreadsheet. In the destination spreadsheet I have an import settings sheet which has a column which contains the named ranges I want to copy from the source spreadsheet and I also have a column which contains the named ranges for the destination of the copied data.

I have trouble with defining the destination range and source range. I would like the script to got through each row and set the destination and source ranges to the named ranges specified in relevant columns in the import setting sheet and copy the data from source to destination.

Any help will be greatly appreciated.

Public Sub grabIRR()   
Dim temp_workbook As Excel.Workbook
Dim filepath As String
Dim filename As String
Dim source_range As String
Dim dest_range As String
Dim IRR_Row As Integer

Application.Calculation = xlCalculationManual
Set temp_workbook = Workbooks.Open(filename:=link_to_IRR, ReadOnly:=True)
Application.Calculation = xlCalculationManual

shtPrivateEquity.Cells.ClearContents

For IRR_Row = 15 To 110
    dest_range = Worksheets("import_settings").Range(IRR_Row, 11).Value.RefersToRange
    source_range = Worksheets("import_settings").Range(IRR_Row, 9).Value.RefersToRange
    shtPrivateEquity.Range(dest_range).Value = temp_workbook.Names(source_range).RefersToRange.Value
Next IRR_Row
On Error GoTo 0

Application.Calculation = xlCalculationAutomatic
temp_workbook.Close savechanges:=False

End Sub

I presume you're getting run-time error 1004 at dest_range = Worksheets("import_settings").Range(IRR_Row, 11).Value.RefersToRange? — Mathieu Guindon

Sam Sam · Accepted Answer · 2019-01-22T16:21:47

I think it will be easier to work with the names as strings rather than range objects.

Try this:

For IRR_Row = 15 To 110
    dest_range_name = Worksheets("import_settings").Cells(IRR_Row, 11).Value
    source_range_name = Worksheets("import_settings").Cells(IRR_Row, 9).Value
    shtPrivateEquity.Range(dest_range_name).Copy Destination := temp_workbook.Range(source_range_name)
Next IRR_Row

Note that I changed some variable names and changed the illegal Range(row, column) to Cells(row, column).

Set the range based on a cell input which a named range

1 Answers