Copy range of values between workbooks

Question

I am trying to copy either single cells values or rows of cells from a source workbook to a target workbook.

The user will have three workbooks open:

Dashboard workbook
Source workbook
Target workbook

The sub reads the user input in Dashboard workbook, which will look like the following:

Source cells    Target cells    Cell/Row
G28             H30             Cell
G29             H31             Row

The sub is then supposed to look up cell G28 in Source workbook and copy that into H30 in Target workbook. Likewise the sub is supposed to look up cell G29 in Source workbook and copy that cell and everything to the right into H31 in Target workbook.

I managed to copy single cell values. I have not been able to implement the functionality for the row type input.

I indicated below where the error is.

Sub transferSub()

Dim wbMain As Workbook: Set wbMain = ThisWorkbook
Dim wbMainDashboard As Worksheet: Set wbMainDashboard = wbMain.Worksheets("Dashboard")

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'Definition of file path for source and target workbooks
sourceModel = wbMainDashboard.Range("FILE_SOURCE") 'Pull from dashboard input
targetModel = wbMainDashboard.Range("FILE_TARGET") 'Pull from dashboard input

'Source and target workbooks
Dim wbSource As Workbook: Set wbSource = Workbooks(sourceModel) 'Workbook already open
Dim wbTarget As Workbook: Set wbTarget = Workbooks(targetModel) 'Workbook already open

'Source and target worksheet
Dim wskpInput_source As Worksheet: Set wskpInput_source = wbSource.Worksheets("INPUT (kp)")
Dim wsSCEInput_source As Worksheet: Set wsSCEInput_source = wbSource.Worksheets("INPUT (SCE)")
'Source and target worksheet
Dim wskpInput_target As Worksheet: Set wskpInput_target = wbTarget.Worksheets("INPUT (kp)")
Dim wsSCEInput_target As Worksheet: Set wsSCEInput_target = wbTarget.Worksheets("INPUT (SCE)")

'Procedures
Dim rng As Range: Set rng = wbMainDashboard.Range("Dashboard!E9:E15")
Dim i As Integer
For i = 1 To rng.Rows.Count
    cell_source = rng.Cells(i, 1)
    cell_target = rng.Cells(i, 1).Offset(0, 1)
    cell_cellrow = rng.Cells(i, 1).Offset(0, 3)

    If cell_cellrow = "Cell" Then 'If cell then copy paste value in that cell
        wskpInput_target.Range(cell_target) = wskpInput_source.Range(cell_source).Value
    ElseIf cell_cellrow = "Row" Then 'If row then copy and paste the row of cells
        wskpInput_source.Range(cell_source, cell_source.End(xlToRight)).Copy _
            wskpInput_target.Range(cell_target)  '---NEED HELP WITH THIS PART---
    End If
Next

End Sub

Note - best practice is to declare all variables. You have a few that are not. Add Option Explicit to the top of the module. It looks like cell_source is a String - and you can't call .End on a String - i.e. cell_source.End(xlToRight). — BigBen
If cell_source (etc) are strings (addresses) then you probably need range(cell_source).End(xlToRight). — SJR
FYI - wbMainDashboard.Range("Dashboard!E9:E15") is a little redundant. Since you already declared wbMainDashboard and are correctly qualifying the Range() with that worksheet, you could just do wbMainDashboard.Range("E9:E15") — BruceWayne

mdelapena mdelapena · Accepted Answer · 2019-10-18T16:18:25

Well, the Range object can either get Cells as arguments or a String (details here).

Hard-coding the range with a string argument would look like this:

wskpInput_source.Range("G28:L28").Copy _ 
destination:=wskpInput_target.Range(cell_target)

but since you already have a variable containing the first cell ("G28") in the row, we only need to find the last cell, you can get it with a Function like the following:

Function GetLastCellInRow(sheetName As String, firstCell As String) As String

   Sheets(sheetName).Range(firstCell).End(xlToRight).Select
   GetLastCellInRow = ActiveCell.Address

End Function

and this is how you call it

'MySheet is the source sheet, so you need to modify that
cell_source_last = GetLastCellInRow(MySheet.Name, cell_source)

And putting all together:

cell_source = rng.Cells(i, 1)
cell_target = rng.Cells(i, 1).Offset(0, 1)
cell_cellrow = rng.Cells(i, 1).Offset(0, 3)
'MySheet is the source sheet, so you need to modify that
cell_source_last = GetLastCellInRow(MySheet.Name, cell_source)

If cell_cellrow = "Cell" Then 'If cell then copy paste value in that cell
    wskpInput_target.Range(cell_target) = wskpInput_source.Range(cell_source).Value
ElseIf cell_cellrow = "Row" Then 'If row then copy and paste the row of cells
    wskpInput_source.Range(cell_source & ":" & cell_source_last).Copy _
        Destination:=wskpInput_target.Range(cell_target)
End If

Copy range of values between workbooks

1 Answers