0

ActiveSheet.Paste gives '1004' error, then I try to debug it. But there is no error while debugging. How does it occur? Is there a solution, recommodation? Note: All variables are defined as global.

For Each dosya In CreateObject("Scripting.FileSystemObject").GetFolder(fileDirec).Files

    Set yeni = CreateObject("Excel.Application")

    yeni.Workbooks.Open fileDirec& dosya.Name

    Set s1 = yeni.Workbooks(dosya.Name).Sheets(1)

    s1.Range("a2:s" & s1.[a65536].End(3).Row).Copy

    sat = Range("a65536").End(3).Row

    Cells(sat + 1, "a").Select

    ActiveSheet.Paste

    yeni.CutCopyMode = False
    yeni.Quit

    Set yeni = Nothing
Next
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Mosman
  • 13
  • 3
  • [Why `.Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook` etc... should be avoided](https://stackoverflow.com/a/10718179/1422451). – Parfait Mar 16 '20 at 14:05
  • I also add "MsgBox ActiveWorkbook.Name & "-" & ActiveSheet.Name" between all the lines. Result is always same. ActiveWorkbook and ActiveSheet didnt change. Then I started the code again and click ok all the msgbox manually. Code run correctly. Then I deleted Msgbox lines then I took Run-time error 1004 ! – Mosman Mar 17 '20 at 11:38

1 Answers1

0

it seems as if you are copying and setting the location to copy

but you have no set the location to select, or atleast which sheet.

maybe try;

thisworkbook.sheets("Sheet1").cells(1,1).value = thisworkbook.sheets("Sheet2").cells(1,1).value 'cells can be replaces with a range

or an alternative could be;

with thisworkbook

sheets("Sheet1").copy 'insert a range if you have
sheets("Sheet2").pastespecial 

end with

try and separate the tasks required to be performed into the designated sheets or maybe workbooks

kuv
  • 36
  • 3
  • Your recommendation does not work. Actually, I add "MsgBox ActiveWorkbook.Name & "-" & ActiveSheet.Name" between all the lines. Result is always same. ActiveWorkbook and ActiveSheet didnt change. – Mosman Mar 17 '20 at 11:30