Copy and paste data from on workbook into an already opened workbook

Question

I'm fairly new to VBA and not sure whether this could be done or not.

I want to paste two rows data of data into an already opened workbook.

I'll try to explain what i want in a bit more with an example.

Consider a workbook "A" where data is entered manually by other people. Workbook "B" will remain open on my system. The first row in workbook "B" will have headers. I want data from the last 2 rows of workbook "A" to be copied and then pasted into workbook "B", which is already open, after inserting 2 new rows below the headers. Suppose row 10 and row 11 are the last 2 rows in workbook "A", then these two rows should be copied and then pasted into workbook "B" at row 2 and row 3 after inserting 2 new rows at the top. Data from row 10 of workbook "A" should be pasted in row 3 of workbook "B" and row 11 from workbook "A" should be copied in row 2 of workbook "B". Workbook "B" will remain open with me only all the time and others will have access workbook "A".

I really don't know whether this can be done or not and because of that reason i was not able to come up with any VBA Code which i can present here.

Because of that reason I thought of asking here. Hoping to get some guidance from the experts here. Thanks in advance

Yes it can be done. Here is the logic for it. **1.** Identify your objects. For example `Set wbThis = ThisWorkBook` for `Workbook A` and `Set wbThat = Workbooks("B")` for `Workbook B` **2.** Similarly set your relevant worksheets. `Set wsThis = wbThis.Sheets("Sheet1")` and `Set wsThat = wbThat.Sheets("Sheet1")`. Change the name of the workbook and worksheets as required. **3.** [Find last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) in `wsThis` — Siddharth Rout, May 28 '21 at 04:55
**4.** Copy last row from `wsThis` and insert copied row in `wsThat` at 2nd row. Record a macro to see how to do that. **5.** Repeat the step for `(Lastrow-1)`. — Siddharth Rout, May 28 '21 at 04:56

score 0 · Answer 1 · answered May 28 '21 at 05:30

Copy Rows

Adjust the values in the constants section.

Option Explicit

Sub copyRows()
    
    ' Constants
    
    Const swbName As String = "A.xlsx" ' Source Workbook Name
    Const sName As String = "Sheet1" ' Source Worksheet Name
    
    Const dName As String = "Sheet1" ' Destination Worksheet Name
    Const dFirst As Long = 2 ' Destination Insert Row
    
    Const rRows As Long = 2 ' Number of rows to be inserted (copied)
    
    ' Destination
    
    ' Workbook/Worksheet
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dName)
    
    ' Source
    
    ' Workbook/Worksheet
    Dim swb As Workbook: Set swb = Workbooks(swbName)
    Dim sws As Worksheet: Set sws = swb.Worksheets(sName)
    
    ' Attempt to find the last non-empty cell.
    Dim sCell As Range
    Set sCell = sws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If sCell Is Nothing Then Exit Sub ' no data in worksheet
    
    ' Write the row of the last non-empty cell to a variable (Source Last Row).
    Dim sLast As Long: sLast = sCell.Row
    
    ' Validate Source Last Row
    If sLast < rRows Then Exit Sub ' too few rows of data
    
    ' Insert/Copy
    
    Dim r As Long ' Destination Rows Counter
    
    For r = 1 To rRows
        dws.Rows(dFirst).Insert
        sws.Rows(sLast - rRows + r).Copy dws.Rows(dFirst)
    Next r
    
End Sub

Copy and paste data from on workbook into an already opened workbook

1 Answers1

Copy Rows