Copy data from current work to another [Closed]

Report
Posts
2
Registration date
Saturday March 15, 2014
Status
Member
Last seen
April 13, 2014
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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

5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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)
Its not able to resolve. It as I am very new to his field of VBA
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Posts
2
Registration date
Saturday March 15, 2014
Status
Member
Last seen
April 13, 2014

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 .
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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