Copy data from one spreadsheet to another

Question

I am trying to copy data from one spreadsheet to another (which will already have data in). I need to copy (from source spreadsheet) all populated cells to column B in the destination spreadsheet but at the last row (not overwriting existing data)

here is what I have so far but it is not exactly what I need and is throwing an error saying:

object required

at this line: Last_Row = Range("A" & Rows.Count).End(xlUp).Row)

And I'm trying to do this in a .VBS file

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("D:\Test2.xlsx")
Set objWorksheet = objWorkbook.Worksheets("Sheet 2")

Set objRange = objWorksheet.Range("A5:D5")
objRange.Copy

'destination spreadsheet stuff:
Set objExcel2 = CreateObject("Excel.Application")
objExcel2.Visible = True
Set objWorkbook2 = objExcel.Workbooks.Open("D:\Test1.xlsx")
Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")   

Last_Row = Range("A" & Rows.Count).End(xlUp).Row
objWorksheet2.Range("B" & Last_Row).Activate
objWorksheet2.Paste

VBA = Visual Basic for Applications. It is a hosted language available primarily in MS Office applications. — David Zemens, Dec 01 '15 at 16:26
From where are you running this code? You're creating TWO new instances of Excel application. This is almost certainly not optimal. — David Zemens, Dec 01 '15 at 16:28
from my laptop. this might not be the best way of doing this or even the best language. I am not a programmer so need any help possible :) — user2026188, Dec 01 '15 at 16:34
Your code does not do want you want at all. If you want it to copy all data from a spreadsheet you should be using UsedRange to return the range that has data. You definitely cannot just set a range as A5:D5 — Clint Street, Dec 01 '15 at 16:34
@ClintStreet Yes, you can absolutely set a range as A5:D5. Did you try it? — David Zemens, Dec 01 '15 at 16:35
"from my laptop" -- what does this mean? How are you executing this code? — David Zemens, Dec 01 '15 at 16:36
@DavidZemens I know you can but the op says this " I need to copy (from source spreadsheet) all populated cells to column B". I am hinting that the UsedRange would work best to get all populated cells. — Clint Street, Dec 01 '15 at 16:37
While that may be an issue for OP eventually, at least for the time being, *that* does not appear to be the problem as indicated in OP, which is a very specific error on a specific line. @ClintStreet — David Zemens, Dec 01 '15 at 16:39
http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — David Zemens, Dec 01 '15 at 16:39
If you qualify the Range with objWorksheet2 as `Last_Row = objWorksheet2.Range("A" & Rows.Count).End(xlUp).Row` then you should not get the error. — PankajR, Dec 01 '15 at 16:39

score 1 · Answer 1 · answered Dec 01 '15 at 16:34

I changed Last_Row to Next_Row because, you'll want to paste on the next line after the data (hence + 1).

And got rid of the second Excel instance, because pasting between two instances is very limited and programmatically very inefficient!

So this should work just fine :

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("D:\Test2.xlsx")
Set objWorksheet = objWorkbook.Worksheets("Sheet 2")

Set objRange = objWorksheet.Range("A5:D5")
objRange.Copy

'destination spreadsheet stuff:
Set objWorkbook2 = objExcel.Workbooks.Open("D:\Test1.xlsx")
Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")

Next_Row = objWorksheet2.Range("B" & objWorksheet2.Rows.Count).End(xlUp).Row + 1
objWorksheet2.Range("B" & Next_Row).Paste

I get an unknown error from this. I should probably have explained I am trying to execute it from a .vbs file. — user2026188, Dec 01 '15 at 16:51
Indeed it would have been good to know! Not sure how to do this in VBS though... :/ — R3uK, Dec 01 '15 at 16:56

Copy data from one spreadsheet to another

1 Answers1