(Solved) – Excel Error | Copy Data from multiple excel to one workbook

  • by
(solved)-–-excel-error-|-copy-data-from-multiple-excel-to-one-workbook

I have a file that copy data range from one excel to the main file until it finish copying all excel file in a folder. But the problem is it crashes. It close the file entirely. Below is the formula

Sub kopy()


Dim path As String

path = "\vtr-deptWFMCWFTVTO Hours"
Dim myfile As String

myfile = Dir("\vtr-deptWFMCWFTVTO Hours")
Do While Len(myfile) > 0


    Workbooks.Open (path & myfile)

    'lookup file name

    Range("A11").Select
    ActiveCell.FormulaR1C1 = _
        "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename"")) 1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"
    Range("A11").Select
    Selection.Copy
    Range("B11").Select
    Selection.End(xlDown).Select
    Range("A31").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A11").Select

    Range("A11:J31").Copy


    Windows("MainFile.xlsm").Activate
    Range("A1").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Windows(myfile).Activate
    Range("A11:A31").Select
    Selection.ClearContents
    Application.CutCopyMode = False

   ActiveWorkbook.Close savechanges:=False


myfile = Dir

Loop
  MsgBox "Tapos na po. Mabuhay ang kilusan"
Range("A1").Select
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *