CODEWITHSUNDEEP
excelvba
Adrian
Adrian

Reputation: 9

How to copy data from 2 cells from workbook A and copy to workbook B in a cell and how do I start a for loop until last row/column

I have two questions

I have no clue on how to combine the data and I do not know where to place the variable inside the code for it to loop until its last column.

Dim Tlastrow As Integer
Tlastrow = Cells(1, Columns.Count).End(xlToLeft).Column

 For r = 1 To Tlastrow
Workbooks("InputB.xls").Worksheets("HC_MODULAR_BOARD_20180112").Range("F3:G3").Copy _
        Workbooks("Output.xls").Worksheets("Sheet1").Range("I3")
Next

Upvotes: 0

Views: 36

Answers (1)

Damian
Damian

Reputation: 5174

Try this:

Option Explicit
Sub Paste()

    Dim wsInput As Worksheet, wsOutput As Worksheet, LastRow As Long, C As Range

    Set wsInput = Workbooks("InputB.xls").Worksheets("HC_MODULAR_BOARD_20180112")
    Set wsOutput = Workbooks("Output.xls").Worksheets("Sheet1")

    With wsInput
        LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row 'Last Row with data
        For Each C In .Range("F3:F" & LastRow) 'loop for every row with data
            wsOutput.Cells(C.Row, "I").Value = C & " " & C.Offset(0, 1)
        Next C
    End With

End Sub

This code is assuming you want to paste every row from your input workbook to the output workbook on the same rows, but merging F and G columns. It's just pasting the values, not formulas or formats.

Upvotes: 0

Related Questions