Ask a question Report

Copy from one workbook and paste in another [Solved]

swe - Latest answer on Oct 5, 2010 12:05PM
Hello,
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!
Read more 
Answer
+30
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 empty....so 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


'*********NEW**********

'Acitvate the workbook you just copied the new line to.
Workbooks("Book2.xls").Sheets("sheet2").Activate

'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
Reply
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

Workbooks("Book2.xls").Sheets("Sheet2").Activate

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
Reply
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.
Reply
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
Reply
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.
Reply
Add comment
Answer
+7
moins plus
100% it will work.

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

D:\Temp

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
ThisWorkbook.Worksheets(1).Activate
Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
SrcBook.Close
Next
End Sub
Add comment
Answer
+2
moins plus
hi,
try this:
Range("A1:A9").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
you can vary the range as per your worksheet.
Add comment
This document entitled « copy from one workbook and paste in another » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.