Copy from one workbook and paste in another

Solved/Closed

swe - Mar 20, 2009 at 10:23 PM
Raghuraman - Oct 5, 2010 at 12:05 PM

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!

Copy from one workbook and paste in another
Copy and paste fonts - Guide
How to paste photo in resume - Guide
Root directory is full or error in pasting - Android Forum
Pi copy and paste - Guide
Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - Excel Forum

3 responses

Answer 1 / 3

swe
Mar 21, 2009 at 07:57 AM

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 21, 2009 at 06:23 PM

Hi,

I tried the following code.It seems to be working but still have problems while pasting the row.
Note:I tried working between the sheets in the same workbook.
Please help.Any help would be appreciated.
Thanks!

Here is my code.

Sub FindLastCell()
Dim LastCell As Range
Dim EmptyCell As Range
With ActiveSheet
Sheets("Sheet1").Select
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
Rows(LastCell).Select
Application.CutCopyMode = False
Selection.Copy
End If

'Rows("262:262").Select
'Application.CutCopyMode = False
'Selection.Copy
'Sheets("Sheet2").Select
'Rows("26:26").Select
'ActiveSheet.Paste

Sheets("Sheet2").Select
Set EmptyCell = .Cells(.Rows.Count, "B").End(xlUp)

'If IsEmpty(EmptyCell) Then
'Do nothing
'Else
Set EmptyCell = EmptyCell.Offset(1, 0)
Rows(EmptyCell).Select

'Range(Range("B" & EmptyCell), Range("IV" & EmptyCell.Row).End(xlToLeft)).Select

ActiveSheet.Paste

' End If
End With
End Sub

WutUp WutUp > swe
Mar 21, 2009 at 10:25 PM

Maybe this will get you in the right direction.

Sub TransferData()

'This is just a workbook I used on my computer. Change to suit your file name.
'Open the workbook you want to copy from.
Workbooks.Open Filename:="C:\Users\family\Desktop\Excel Files\AutoFill.xls"

Set c = Workbooks("AutoFill").Sheets("Sheet1") 'This is the workbook you want to copy the data from.
Set d = Workbooks("Copy from Different Workbooks").Sheets("Sheet1") 'This is the destination workbook.
'Change the workbook names to your own workbook names.

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 (the empty row).
'That is why +1 is used. Otherwise, you would write over the existing data.
d.Rows(NewRow + 1).Value = c.Rows(LastRow).Value

End Sub

swe > WutUp WutUp
Mar 22, 2009 at 12:28 AM

Thanks a lot. It really works.

But have a problem while initially opening the workbook.
i tried not to invoke the workbook.Instead directly opened it and ran from there.It works.

I need furthur assistence with saving this file.
After I paste this new row I need to Rename the AOR Summary spreadsheet with the current Monday's date
(format: AOR Summary mm-dd -yy)
This date can be caputured from the row that we inserted.
The date is always in column E.

Please Help.

WutUp WutUp > swe
Mar 22, 2009 at 12:44 PM

Sub TransferData()

'This is just a workbook I used on my computer. Change to suit your file name.
Workbooks.Open Filename:="C:\Users\family\Desktop\Excel Files\AutoFill.xls"

Set c = Workbooks("AutoFill").Sheets("Sheet1") 'This is the workbook you want to copy the data from.
Set d = Workbooks("Copy from Different Workbooks").Sheets("Sheet1") '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("Copy from Different Workbooks").Sheets("Sheet1").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("Copy from Different Workbooks").SaveAs Filename:="AOR Summary" & MyDate & ".xls", FileFormat:=xlWorkbookNormal

End Sub

swe > WutUp WutUp
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

Answer 2 / 3

Raghuraman
Oct 5, 2010 at 12:05 PM

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

Answer 3 / 3

museum74 Posts 4 Registration date Sunday March 1, 2009 Status Member Last seen June 8, 2009 7
Mar 21, 2009 at 03:11 AM

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.

Similar discussions

Can no longer paste text into insta

How can I make my name text invisible in Discord

Root directory is full or errors during pasting

Deleted Message icon

Roots directory fulls or errors during parsing paste

Similar discussions

Subscribe To Our Newsletter!