CODEWITHSUNDEEP
vbaexcelvbscript
user2026188
user2026188

Reputation: 45

Copy data from one spreadsheet to another

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

Upvotes: 2

Views: 182

Answers (1)

R3uK
R3uK

Reputation: 14537

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

Upvotes: 1

Related Questions