Report

Copy from one workbook and paste in another [Solved/Closed]

Ask a question swe - Last answered on Oct 5, 2010 at 12:05 PM by Raghuraman
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!
Helpful
+30
plus moins
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.
Was this answer helpful?  
swe- Mar 22, 2009 at 02:44 PM
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
WutUp WutUp- Mar 22, 2009 at 04:18 PM
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
swe- Mar 23, 2009 at 09:06 AM
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 at 06:47 PM
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 at 08:50 PM
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.
Helpful
+7
plus moins
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
Helpful
+4
plus moins
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.

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!