Run-time error '1004': Microsoft Excel cannot paste the data.

[Ksar87]

Hi,

I have a macro to open files -> copy data from a1:b800 -> put it to general file and doing solver function. But when I'm start a macro I'm get back information "Run-time error '1004': Microsoft Excel cannot paste the data." Frequency of this message is 80% of time when I'm start a macro.

I don't know what I need to do to solved this problem.
Below You can see part of the code:

Sub bijsteladvies()   
Application.ScreenUpdating = False
Application.DisplayAlerts = False '
   
' CHECK OR IMPORT DATA IS GOOD
If Not (Range("checkdata")) Then
    MsgBox ("Well good to fill all data !!!!!!!!")
    GoTo Lastline:
End If




' CHECK THAT EXISTS FILE WITH LAMP NUMBER




    Dim Search_file_name As String, Found_file_name As String
    Search_file_name = Range("subdirectory") & Range("lampnur") & "*.*"
    Found_file_name = Dir(Search_file_name)
    If Found_file_name = "" Then
        MsgBox ("lamp number not found")
        GoTo Lastline:
    End If
    Range("naam") = Range("subdirectory") & Found_file_name
    
    ' OPENEN FILE
    Workbooks.OpenText Filename:=Range("naam"), Origin:= _
    xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1)), ThousandsSeparator:=","




'COPYING DATA to "bijstel file"
Range("a1:a800").Select
Selection.Copy
ActiveWindow.Close
Range("s2").Select
ActiveSheet.Paste




' OPENEN FILE
    Workbooks.OpenText Filename:=Range("naam"), Origin:= _
    xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1)), ThousandsSeparator:=","




'COPYING DATA to"bijstel file"
Range("b1:b800").Select
Selection.Copy
ActiveWindow.Close
Range("t2").Select
ActiveSheet.Paste

...

When I'm click the debug then VBA marks me first line with "ActiveSheet.Paste". When macro do it (this "ActiveSheet.Paste") then finish all lines on a macro. If You need more information about the macro code then I can explain.

Regards

Ksar87

 

[bobsan42]

There are a few possible reasons:
1. (less likely) you try to paste on a protected sheet
2. you are losing the copied data
3. (highly unlikely) There is no Active sheet

I would bet on option no. 2 although I am not really sure why it is not a permanent problem.
because you suppress the system messages (Alerts) you don't see a dialogue box asking if you want to keep the data.
And you may be losing it because you closed the source file before you did the actual paste.
I am not a 100 % sure but I suspect the copy/paste methods of some of the MS office products are not using the clipboard as one would expect.
Try to open the files as objects, then copy-> paste and just then close the opened object. Or use Workbooks("....textfilenamehere........").Close after pasting the data.
And IMO it is better to use a fully specified reference instead of Activesheet.

 

[Ksar87]

Thx bobsan42 for Your answer. But after when macro falls I choose "end" and I can past data. I have this data on clipboard. Other reasons: sheets is not protected and I have active sheet...

 

[bobsan42]

My suggestion still stands - do not close the file you copy from before you finish pasting.
Also check after pasting if you have external links in the pasted data.

 

[Ksar87]

Bobsan42 thx a lot for Your help. Macro working very good.

Topic is closed.

Regards Ksar87