Copying cell range from a workbook sheet to another workbook sheet

Question

I have 3 workbooks, namely: A, B and C.

I'm coding a VBA macro inside A in order to copy cell range content of a particular sheet from B to C.

Dim wb_TC_PBS As Excel.Workbook
Dim wb_SPO_PBS As Excel.Workbook

Set wb_TC_PBS = Workbooks.Open("C:\temp\migration\B.xlsm")
Application.CutCopyMode = False
FinalRow = Sheets("TC_PBS").Cells(Rows.Count, 1).End(xlUp).Row
Range("A5:EO" & FinalRow).Copy

I would like to understand if my approach is correct and how to continue pasting content on workbook C

After Range("A5:EO" & FinalRow).Copy remove this line Application.CutCopyMode = False because it removes what you've just copied from the clipboard it's equivalent to pressing ESC key after you copy some range in excel. Then Activate and select the range and sheet where you want to paste. — Stupid_Intern
thx, I need this command; because I've tried something like this, but doesn't work wb_SPO_PBS.Sheets("Input").Offset(5).Insert Shift:=xlDown — Noomak

Stupid_Intern Stupid_Intern · Accepted Answer · 2016-04-02T18:59:02

Here this might help

Dim a As Workbook,b As Workbook, c As Workbook, FinalRow As Long

Set a = ThisWorkbook
Set b = Workbooks.Open("C:\temp\migration\B.xlsm")
Set c = Workbooks.Open("C:\temp\migration\C.xlsm")

b.Activate
FinalRow = b.Sheets("TC_PBS").Cells(Rows.Count, 1).End(xlUp).Row
b.Sheets("TC_PBS").Range("A5:EO" & FinalRow).Copy
c.Activate
c.Worksheets(1).Range("A1").Select
c.Sheet1.Paste
Application.CutCopyMode = False

Copying cell range from a workbook sheet to another workbook sheet

1 Answers