I am getting error as "Run Time error '1004' Microsoft excel cannot paste the data

Dear All,

I am not that much familier with VBA. i am getting error as Run Time error '1004' Microsoft excel cannot paste the data in following VBA code.

Sub copydata()
Dim folderpath As String, filepath As String, filename As String
folderpath = "C:\Users\09395\Desktop\Final CFCM\"
filepath = folderpath & "*.xls*"
filename = Dir(filepath)
Dim lastrow As Long, lastcoloumn As Long
Do While filename <> ""
Workbooks.Open (folderpath & filename)
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 47))
filename = Dir
Loop
Application.DisplayAlerts = True
End Sub

Please read the forum rules, especially #3 about using code tags.

I think I misjudged the issue, so I'm changing this post, I recommend specifying the workbook you are using, and avoiding "active" where possible when working with multiple workbooks. Also, you don't need to specify the whole destination range, just the first cell to paste in, the paste area should match the source. Try this modification:

Option Explicit
Sub copydata()

Dim folderpath As String, filepath As String, filename As String
Dim lastrow As Long, lastcoloumn As Long, erow as Long
Dim wb As Workbook
Dim ws As Worksheet

folderpath = "C:\Users\09395\Desktop\Final CFCM\"
filepath = folderpath & "*.xls*"
filename = Dir(filepath)
Do While filename <> ""
    Set wb = Workbooks.Open(folderpath & filename)
    Set ws = wb.ActiveSheet
    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    erow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ws.Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy Destination:=ThisWorkbook.Worksheets("Sheet1").Cells(erow, 1)
    Application.DisplayAlerts = False
    wb.Close
    filename = Dir
Loop
Application.DisplayAlerts = True

End Sub

@ Arkadi,

I explicitly ask you, where is the "Option Explicit", you should have included in your proposed solution!!!

Please stop testing my temper.LOL

@Amittapre,

Please adhere to Arkadi's request to Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Regards.

@ winon, ouch! You got me again.... I strictly adhere to using option explicit in my own code, yet often forget to use it when providing solutions MY BAD! I have edited my post accordingly

This line:

lastcolumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column

finds the last column, and then that is what gets used for the copy... If you don't want all the columns, then you could simply change that line to:

lastcolumn = 9

That way the copy code will use column 9 as the last one.

Please take a moment to edit your first post to include code tags for the code? Both Winon and I have asked this before, it will make it easier other readers in the future.

"Not happening properly" does not help me very much to see what is wrong?

but this line of your code:

erow = ThisWorkbook.Worksheets("CFCM data").Cells(Rows.Count, 1).End(xlUp).Offset(9, 0).Row

should be

erow = ThisWorkbook.Worksheets("CFCM data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

because the offset just makes it 1 row below the last row with data, and when you use 9 it means it pastes too low (9 rows below last with data)

Hello Amittapre,

Your reply in Post#15 is noted, and I have subsequently decided to remove the request for Code Tags in you first post.

Thank you!

How about:

Sub copydata()

Dim folderpath As String, filepath As String, filename As String
Dim lastrow As Long, lastcolumn As Long

folderpath = "C:\Users\09395\Desktop\Final CFCM\"
filepath = folderpath & "*.xls*"
filename = Dir(filepath)
Do While filename <> ""
    Workbooks.Open (folderpath & filename)
    lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    erow = ThisWorkbook.Sheets.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy ThisWorkbook.Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 47))
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    filename = Dir
Loop
Application.DisplayAlerts = True

End Sub

It is just a quick guess, not tested.

Hello Amitappre,

Regarding your PM "Re: Please understand the urgency"

Winon,

Atleast understand the urgency of Work. how to can you tell to stop reply. This one kind of Ignorance doing by you to individual. Kindly confirm with the steps how to change the original post as i have tried thrice. Atleast check once after that i have reply as per forum rule or not. Not expected. Feeling demotivated.

Your response to my Moderation in your Thread, was sufficient and does not call for this kind of an approach via a PM. Arkadi has, and is doing his best to assist you with your issue, and if it was that "URGENT", you would already by now have given him some kind of feedback.

Personally I don't take kindly to your way of trying to coercing me to URGENTLY attend to your issue. I also have no doubt in Arkadi's capabilities, and he should be able to solve your issue. Please also understand that I am working on many other Threads, and afford each my free time, in an order I deem fit.

If your issue is really that URGENT, why not consider posting it in the Commercial Forum, with a reference to this Thread?

Regards.

@ amittapre: if you dont follow forum rules then you can expect to have your thread put on hold. If it is so important then you would take the time to follow them. We do this 100% for free, giving our time to help others like you, and we enjoy doing it. If you find it necessary to be disrespectful, then we will not be motivated to help.
You did not answer my post so I dont know if it helped or not.... Respond with details of what is wrong or provide a clear example.
@Winon, I appreciate your confidence my friend, and after the way you were spoken to, I am tempted to just stop helping the OP.... that tone is inexcusable! Just say the word and Ill forget about this thread if you wish it.

@ Arkadi,

Granted, I am a bit peeved off.

Thank you for the kind words, however please feel free to use your own good judgement and prerogative.

Thank for the support my Friend.

Kind regards.

Hello Jamesera27,

Nice Catch Indeed!

Thank you.

@Jamesera27 Good observation!
@Winon I will wait to see what OP says. My heart wants to help anyone who needs it but I am loyal to my friends also and they must be respected especially when they are right.

Amittapre,

Sorry I had not read code very well, yes that line in your code is no good. Which sheet should be used to find erow?:

erow = ThisWorkbook.Sheets("SheetNameHere").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Since you are pasting the data in column A, in the row indicated by erow, and when you paste the data will go across the cells, you don't need to specify the full range, just the cell you are pasting in, try this:

Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy ThisWorkbook.Worksheets("CFCM data").Range("A" & erow)

@Amittapre,

... or else dont stop anyone to help anyone.

I don't believe you are qualified to tell me how to go about my actions on this Forum.

@ Winon, I think that comment sounded worse than it was intended due to a language barrier?
@ Amittapre, Winon acknowledged that your NEW post with code tags when you could not edit your original was a good solution (in post #13). He then removed the moderation request that asked others not to answer. That is why I continued helping, and for the record Winon works VERY hard to help everyone, not only solving problems, but always with other ideas for improvement. He is an awesome example to us all on the forum, please keep that in mind... his intentions were not malicious, and he did what any of us would have done to be helpful but also to keep order.

@Arkadi,

@ Winon, I think that comment sounded worse than it was intended due to a language barrier?

I must point out however, that I did not find it difficult to understand what he was conveying in other replies.

Thank you for your loyal support my Friend. Friends like you are hard to find.

Keep up your good work!

Kind Regards.

Amittapre,

Is this thread solved? Or do you still have problems?

Thank you Amittapre,

@Winon,
I apologies, I accept my mistakes.

Now, that is much better,

Perhaps we can start afresh, and if you need us to assist you further, please just ask!.

I see some confusing code:
This means lastrow is calculated based on column 10 (J) in the workbook you open

lastrow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row

This means last column is calculated based on Row 10 of the workbook you open:

lastcolumn = ActiveSheet.Cells(10, Columns.Count).End(xlToLeft).Column

This means you copy a range that starts at J2, and goes to last column and last row:

ActiveSheet.Range(Cells(2, 10), Cells(lastrow, lastcolumn)).Copy

Are you sure "10" is correct?? it seems strange to me. I think the paste is fine, and lastrow and/or last column are the problem, and maybe also the first cell of the range you copy.

Since it relies on a second workbook, it is hard to understand what is wrong... What do you mean by "paste is not working properly"

@ Arkadi,

You make me sick for being correct.LOL

Since it relies on a second workbook, it is hard to understand what is wrong

Amittapre, we do not have access to the "other" Workbook.

Have you tried to open the other workbook before running your Code?

Also, why don't you Google Copying from a closed Workbook to specific range in active Workbook?

Hi Amittapre,

You see, Arkadi is GOOD!

Please be so kind as to add to his Reputation by clicking on the Star next to Add Reputation at the bottom left of his post.

Thank you!

@ Winon, sadly I don't feel like I'm so good.... every time I had an idea I posted without reviewing ALL of the logic. I should have seen that Row 10 and Col 10 are unusual for lastrow/column calculations. It is of course why sample workbooks can be so useful.
I do appreciate your comments and support my friend!

@Amittapre, I'm glad we could finally fix the problem thanks for letting us know!