Copy Selected Rows to next blank Row of another workbook, then second workbook

acat · Oct 18th 2019

Hello Wizards,

On Excel worksheet of workbook A, manually I select some Rows (not range, but rows)

Need VBA to copy these rows to next blank Row of workbook B, save B
Then copy same rows to next blank Row of workbook C, save C

Worksheet in workbook B and C is: Sheet1(ALL)

B – path C|123
C – path K|456

**royUK** · Oct 18th 2019

Untested, but try this

Code

Option Explicit


Sub CopySelection()
    Const bPath As String = "C\123\B.xlsx"
    Const cPath As String = "K\456\C.xlsx"
    Dim oWb As Workbook
    Dim rRng As Range


    Set rRng = Selection


    Set oWb = Workbooks.Open(bPath)
    rRng.Copy ActiveWorkbook.Sheets("All").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    oWb.Close True
    Set oWb = Workbooks.Open(bPath)
    rRng.Copy ActiveWorkbook.Sheets("All").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    oWb.Close True
End Sub

Display More

acat · Oct 18th 2019

How do I change this line to refer to a sheet not by it's name on tab (e.g. "All") but by code name, I believe it's called, so even if tab name will change code will still work?

Code

rRng.Copy ActiveWorkbook.Sheets("All").Cells(Rows.Count, 1).End(xlUp).Offset(1)

**royUK** · Oct 18th 2019

You can't do that easily. because referring to a sheet by its codename always implies the sheet is in ThiswWorKBook

Copy Selected Rows to next blank Row of another workbook, then second workbook

Participate now!

Share