Loop through range and copy paste between workbooks

Question

I am trying to copy and paste data between two workbooks. I am using a third separate workbook where the user can indicate the copy range, the paste range and indicate if it is a cell or row that is to be copy-pasted. The layout is as follow:

    Source      Target      Cell/Row
    G29         G29         Cell
    G30         G32         Cell
    G31         G33         Row

For example based on the above the VBA code is supposed to copy what is in cell G29 in source workbook and paste it in G29 in target workbook and so on. I have defined the "Source" range as rng and loop through the range in order to define the target range and whether it is a cell or row that is to be copy-pasted. However, for some reason I get an error in first defining my cell_source, cell_target and cell_cellrow variables and also get errors when running the loop where I set the target cell in target workbook equal to the cell_source_input variable. I would much appreciate if anyone can help with this.

    Sub transferScript()

    Dim wbMain As Workbook: Set wbMain = ThisWorkbook
    Dim wbMainDashboard As Worksheet: Set wbMainDashboard = wbMain.Worksheets("Dashboard")
    Dim CopyLastRow As Long
    Dim rng As Range: Set rng = Application.Range("Dashboard!E9:E15") 'change to E150 !!

    sourceModel = wbMainDashboard.Range("FILE_SOURCE")
    targetModel = wbMainDashboard.Range("FILE_TARGET")

    Dim wbSource As Workbook: Set wbSource = Workbooks.Open(Filename:=sourceModel)
    Dim wbTarget As Workbook: Set wbTarget = Workbooks.Open(Filename:=targetModel)

    'Source workbook
    Dim wsKpInput_source As Worksheet: Set wsKpInput_source = wbSource.Worksheets("INPUT (KP)")
    Dim wsSCEInput_source As Worksheet: Set wsSCEInput_source = wbSource.Worksheets("INPUT (SCE)")
    'Target workbook
    Dim wsKpInput_target As Worksheet: Set wsKpInput_target = wbTarget.Worksheets("INPUT (KP)")
    Dim wsSCEInput_target As Worksheet: Set wsSCEInput_target = wbTarget.Worksheets("INPUT (SCE)")

    'Error handling
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    Dim i As Integer
    Dim cell_source As String
    Dim cell_target As String
    Dim cell_cellrow As String

    Dim cell_source_input As Variant

    For i = 0 To rng.Rows.Count
        'Definition of source cell, target cell, and cell_row input
        cell_source = rng.Cells
        cell_target = rng.Cells.Offset(rowOffset:=0, columnOffset:=1)
        cell_cellrow = rng.Cells.Offset(rowOffset:=0, columnOffset:=3)

        cell_source_input = wsKpInput_source.Range(cell_source)

        If cell_cellrow = "Cell" Then
            wsKpInput_target.Range(cell_source) = cell_source_input
        End If
    Next

    End Sub

What is the error number and description? What values where in the cell when the error was generated? What are you hoping to achieve with rng.Cells? And rng.Cells.Offset? Your missing properties for both. If you imagine rng as a square then rng.Cells is exactly the same square, that code generates an error and accomplishes nothing. If you're trying to get the value of a single cell then rng.Cells(1) would give you the value of the first cell in rng... But that's not likely something you were trying to do. — ProfoundlyOblivious
I guess you have to change cell_source = rng.Cells to cell_source = rng.Cells(i,1).Address. And you don't need cell_target (since you don't use it) — DisplayName

David García Bodego David García Bodego · Accepted Answer · 2019-10-15T11:33:56

Supposing that there is no mistake on the previous code:

Dim i As Integer
Dim cell_source As String
Dim cell_target As String
Dim cell_cellrow As String

Dim cell_source_input As Variant

For i = 0 To rng.Rows.Count
    'Definition of source cell, target cell, and cell_row input
    cell_source = rng.Cells
    cell_target = rng.Cells.Offset(rowOffset:=0, columnOffset:=1)
    cell_cellrow = rng.Cells.Offset(rowOffset:=0, columnOffset:=3)

    cell_source_input = wsKpInput_source.Range(cell_source)

    If cell_cellrow = "Cell" Then
        wsKpInput_target.Range(cell_source) = cell_source_input
    End If
Next

Should be:

Dim i As Integer
Dim cell_source As String
Dim cell_cellrow As String
Dim cell_source_input As Variant

For i = 0 To rng.Rows.Count
    'Definition of source cell, target cell, and cell_row input
    cell_source = rng.Cells(i,1).Value 'It seems to, but it is not clear with no sample
    cell_cellrow = rng.Cells(i,1).Offset(0, 3).Value
    cell_source_input = wsKpInput_source.Range(cell_source)

    If cell_cellrow = "Cell" Then
        wsKpInput_target.Range(cell_source) = cell_source_input
    End If
Next

Hope it helps... Always be better if you provide some sample of the input and expected output. Anyhow, in the code previous to this procedure there are a few issues: sourceModel is not defined and it seems to be a Range, targetModel is not defined and it seems to be a Range, Workbooks.Open(Filename:=sourceModel) it is trying to open one file with a Filename that it is taking a Range... check them...

Loop through range and copy paste between workbooks

1 Answers