Copy from one workbook and paste in another [Solved]

Ask a question swe - Last answered on Oct 5, 2010 12:05PM by Raghuraman

I need to copy the new record from one workbook and paste it in another workbook.

from the first work book this new record is always the last record in that sheet(last row with data).
And in the second workbook the new record should be the last record(first empty row).

Please help!
See more 
moins plus
Thanks for the Reply.

I want to work between the workbooks.
Copy from first workbook and paste in the 2nd workbook.

In the first workbook the data starts from column B.
I am looking for the current data (this monday ).And this record would be always the last Row with data.
When pasting in workbook2 ,This record should be inserted in the first row without any data.(But column A is always should not look for column A).

I am new to programming and writing macros.
Please Help.
swe- Mar 22, 2009 02:44PM
Thanks for replying.I really apprecitae all your help.

I used your code to work between two different workbooks,but I get an error.
It works great between the sheets in the same work book.But I really want to make it work between 2 different workbooks.
The errors I get is "Run time error '9':Subscriprt out of range."
At this below line of code
Set d = Workbooks("Book2.xls").Sheets("sheet2") 'This is the destination workbook

Sub Transferdata()

'This is just a workbook I used on my computer. Change to suit your file name.
'Workbooks.Open Filename:="Book1.xls"

Set c = Workbooks("Book1").Sheets("sheet1") 'This is the workbook you want to copy the data from.
Set d = Workbooks("Book2.xls").Sheets("sheet2") 'This is the destination workbook

Dim LastRow
Dim NewRow

'In your example coding, you used column B in both workbooks to find the last row. So, that is what I used.
LastRow = c.Range("B65536").End(xlUp).Row
NewRow = d.Range("B65536").End(xlUp).Row

'The destination workbook has to find the last row, and then paste the data below it.
'That is why +1 is used. Otherwise, you would write over the existing data.
d.Rows(NewRow + 1).Value = c.Rows(LastRow).Value


'Acitvate the workbook you just copied the new line to.

'Find the last row of your new line and get the date.
Dim GetDate
Dim MyDate
GetDate = d.Range("E65536").End(xlUp).Row
MyDate = d.Range("E" & GetDate).Value

Workbooks("Book2.xls").SaveAs Filename:="AOR Summary" & MyDate & ".xls", FileFormat:=xlWorkbookNormal

End Sub
WutUp WutUp- Mar 22, 2009 04:18PM
This is what I just ran without any errors between two workbooks NOT between sheets.
I did not get any errors. I am using Office 2007, but saved as 97-2003 woorkbook.
Book1 is closed. Book2 I have open, and the macro (obviously) resides in book two.

It looks like the sheet names are just default names, but I would check again. Make sure there are no extra
spaces, etc. in the sheet name vs. macro code.

Sub Transferdata()

Workbooks.Open Filename:="C:\Users\family\Desktop\Book1.xls"

Set c = Workbooks("Book1").Sheets("Sheet1")
Set d = Workbooks("Book2.xls").Sheets("Sheet2")

Dim LastRow
Dim NewRow

LastRow = c.Range("B65536").End(xlUp).Row
NewRow = d.Range("B65536").End(xlUp).Row

d.Rows(NewRow + 1).Value = c.Rows(LastRow).Value


Dim GetDate
Dim MyDate
GetDate = d.Range("E65536").End(xlUp).Row
MyDate = d.Range("E" & GetDate).Value

Workbooks("Book2").SaveAs Filename:="AOR Summary" & MyDate & ".xls", FileFormat:=xlWorkbookNormal

End Sub
swe- Mar 23, 2009 09:06AM
I still get the same error.
"Run time error '9':Subscriprt out of range."
I am using Excel 2003.
Can we customize this code with few colums in the row.
As there are only 4 columns in the sheet.
like select last row with columns from B:E in woork book 1 and paste as last row in workbook2 (again same columns B:E).

please help.
WutUp WutUp- Mar 23, 2009 06:47PM
Replace the line where the rows are copied to this:

d.Range("B" & NewRow + 1, "E" & NewRow + 1).Value = c.Range("B" & LastRow, "E" & LastRow).Value

I might not be the right person to help you with the workbooks. If you want to rename the file AOR Summary...with the new date everyday, then use that new file to update the next day, you can't refer to it as Book2 in the macro code because you will get an error
swe- Mar 23, 2009 08:50PM
Thanks WutUpWutup!

It finally works between the Workbooks.
I really appreciate all your help and patiently working with me.
I am a beginner at Programming macros & First time at Help forums.
I was skeptical.... But you made my work easy.
Thanks a lot.
Leave a comment
moins plus
100% it will work.

Just copy all your excel files under the below mentioned path.


Open new excel file copy and paste the below formula in the excel VB module .

Sub merched()
Dim SrcBook As Workbook
Dim fso As Object, f As Object, ff As Object, f1 As Object

Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.Getfolder("D:\Temp\")
Set ff = f.Files

For Each f1 In ff
Set SrcBook = Workbooks.Open(f1)
Range("E8:IV" & Range("E65536").End(xlUp).Row).Copy
Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
End Sub
Leave a comment
moins plus
try this:
you can vary the range as per your worksheet.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!