0

I have a folder named "macro" containing about 25 excel workbooks. All of the workbooks are formatted exactly the same, the only difference is the date ranges.

What i would like to do is implement some VBA code that will combine all of these files into one single workbook.

When I try to run this on a folder located on a shared network, I receive an error that a particular excel file cannot be found named "wage audit info" cannot be found and has either been moved or deleted.

The weird part is that no where in the code am I searching for this file, and the file is not located in the folder in the first, so I"m not sure why VBA is searching for this file at all.

Here is my code:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "\\data02\Finance\Dept01\01 FILES\2019 Files\Paydate 4.5.19\EOM DATA\Individual Grouping\Macro\"
    ChDir strPath
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("ExportSheet").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("ExportSheet").Range("A3:I" & LastRow).Copy wkbDest.Sheets("ExportSheet").Cells(Rows.Count, "A").End(xlUp).Offset(0, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Any idea why I am receiving this error?

It's almost as though I am searching in the wrong directory, but I know that I'm not...

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
ranopano
  • 509
  • 2
  • 16
  • 32
  • Take a look at https://stackoverflow.com/questions/31414106/get-list-of-excel-files-in-a-folder-using-vba for an example of how to iterate through files in a directory. – Frank Ball Apr 05 '19 at 21:21
  • Possible duplicate of [Get list of Excel files in a folder using VBA](https://stackoverflow.com/questions/31414106/get-list-of-excel-files-in-a-folder-using-vba) – Egan Wolf Apr 05 '19 at 21:26

0 Answers0