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 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).