Copy-paste between workbooks

Question

I would like to copy-paste a table row by row from one Workbook1 to Workbook2 ( on an interval of time ). In Workbook2 every row copied has its own sheet, the next copy-paste is gonna be on the next empty row. ( in the picture that I've attached Audi row is gonna have its own sheet, where every update is gonna go in the next empty row, the same for the others), here is the code that I tried only for one row of table but it doesn`t works:

Sub Updateeee()
Application.OnTime Now + TimeValue("00:00:15"), "lalala"
End Sub

Sub lalala()
Workbooks("Workbook1.xls").Worksheets("Sheet1").Range("B2:R2").Copy
ActiveCellRow.Offset(1, 0).Activate
Workbooks("Workbook2.xls").Worksheets("Sheet1").Range("B2").Paste
Call Updateeee
End Sub

Table example

Hi @JacobH, it doesn't work, I just edited now the question, thank you! — Amy, May 05 '17 at 19:59
Hi Amy, please consider the link at http://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another. It has multiple solutions for this. — Ulysses, May 05 '17 at 20:01
@StillNestling I've tried first two codes and it doesn't copy, I double checked them too... — Amy, May 05 '17 at 20:30
I figured it out, I just have to find a way that next copy-paste to go on the next empty row... — Amy, May 05 '17 at 21:02

score 1 · Answer 1 · edited May 23 '17 at 11:47

1

To find next empty row:

 rowCount =  wbDest.Sheets("Data").rows.count
 NextEmptyRow = wbDest.Sheets("Data").Range("A" & rows.count).End(xlUp).Row + 1

To paste you may use the solution provided by @Moosli or a lot of examples are given at Copy from one workbook and paste into another including the solution given here so you may use the the one that suits your needs.

Hope that helps.

edited May 23 '17 at 11:47

Community

1
1

answered May 08 '17 at 03:16

Ulysses

5,616
7
48
84

Moosli · Answer 2 · 2017-05-08T08:37:47.380

0

You can try something like this

Workbooks("Name.xlsx").Worksheets(1).Range(Cells(6, 8), Cells(6, 9)).Copy Destination:=ThisWorkbook.Worksheets(2).Range(Cells(6, 8), Cells(6, 9))

Or something like this,

Dim wbSour As Workbook
Dim wbDest As Workbook

'Open WB's
Set wbSour = Workbooks.Open("wbSource.xls")
Set wbDest = Workbooks.Open("wbDestination.xls")

'Copy from one to the other
wbDest.Sheets("sheet1").Range("A1").Value = wbSour.Sheets("sheet2").Range("A1") 

'Close Source file:
wbSour .Close

edited May 08 '17 at 08:37

answered May 06 '17 at 07:56

Moosli

3,140
2
19
45

There is some space between "wbSour" and ".Sheets." (and similarly) that should be removed. – Ulysses May 08 '17 at 02:59

Copy-paste between workbooks

2 Answers2