Copy data from current work to anotherClosed

Question

Hi I am using the below code to copy data from Current Work To Another 

Sub RunMe()
Dim lRow As Integer

lRow = Range("A1").End(xlDown).Row

Range("A2:D" & lRow).Copy
Workbooks.Open "C:\MyFiles\Test.xls" 'Change file path to match yours
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
ActiveWorkbook.Close SaveChanges:=True

Application.CutCopyMode = False
End Sub

But I can use this in Excel 2007 but recently we have shifted to Excel 2010 and I am getting the below Error Run time error '1004' and some Pastespecial object not defined something like that 

Please can you help me in this

venkat1926 · Answer

quote
lRow = Range("A1").End(xlDown).Row Range("A2:D" & lRow).Copy Workbooks.Open "C:\MyFiles\Test.xls" 'Change file path to match yours 
unquote
first open the workbook and activate the sheet and then define IRow (an not before)

sush · Answer

Its not able to resolve. It as I am very new to his field of VBA

venkat1926 · Answer

please post s very small extract of data (you can upload it to speedyshare.com and post the download url) and AGAIN EXPLAIN. It would be difficult to see the explanation to a thread some threads before and so my request for explanation again

Sushil.gaikwad27 · Answer

http://speedy.sh/KmbXW/Book2-3.xlsx


I have a spreadsheet from which I am copying data and pasting it in the mentioned link of macro. the spreadsheet attached has the sample data and while pasting I need the macro to paste the data and the end of the row .

venkat1926 · Answer

the macro seems to be ok. which code gives porblem and what is the error

after you code statement 

Workbooks.Open "C:\MyFiles\Test.xls" 'Change file path to match yours

add a statement
Worksheets("Sheet1").Activate
 ( this may not necessary for the code run but it may copy in some sheet no necessarily the sheet you want)
if it works in 2007 it shd work in 2010 also

is the file you mentioned above , does it exist check

how to copy the macro in xlsx 2007 or above
click developer ribbon
veditor
control+R
see all open files (better close all other files)
select the file you are interested
in the vb menu bar click insert - module
on the right side of vbeditor is the module 
paste the macro there 
save the file as MACRO ENABLE WORKBOOK
CLOSE THE FILE
OPEN AND CLICK OPTION AT THE TOP AND ENABLE MACROS.
RUN THE MACRO

Copy data from current work to another

5 responses

Similar discussions

Subscribe To Our Newsletter!