Hi, I have this macro that works great. But now I have files from 2016 and it won't seem to pick them up, it still picks up all the ones from 2015, can you see something in it that may cause this?
Option Explicit
Dim destFile As Worksheet, rNUM As Long
Sub MainMacroJars()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Call ClearStuff
Set destFile = ThisWorkbook.Worksheets("Accruals Jars")
Call Macro1Jars("L:\Team Folders\Operational (2)\Directional\Clear Operations\Jar Reports\")
'MsgBox "Done Like Dinner!"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub Macro1Jars(Mainfolder As String)
Dim Fldr As Object, FL As Object, SubFldr As Object
Call macro2Jars(Mainfolder & Application.PathSeparator)
Set Fldr = CreateObject("scripting.filesystemobject").GetFolder(Mainfolder)
For Each SubFldr In Fldr.SubFolders
Macro1Jars SubFldr.Path
Next
End Sub
Private Sub macro2Jars(MyPath As String)
Dim FilesInPath As String, MyFiles() As String
Dim FNum As Long
Dim mybook As Workbook
Dim i As Long, lrow As Long
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
FilesInPath = Dir(MyPath & "Jar *.xlsx")
FNum = 0
Do While Len(FilesInPath) > 0
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop
' Loop through all files in the myFiles array.
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(FNum), False)
On Error GoTo 0
If Not mybook Is Nothing Then
'On Error Resume Next
For i = 1 To mybook.Worksheets.Count
If mybook.Worksheets(i).Name Like "Don*" Then
lrow = destFile.Range("A" & Rows.Count).End(xlUp).Row
destFile.Range("A" & lrow + 1).Value = Left(MyFiles(FNum), Len(MyFiles(FNum)) - 5)
destFile.Range("B" & lrow + 1).Value = mybook.Worksheets(i).Range("i11").Value
destFile.Range("C" & lrow + 1).Value = mybook.Worksheets(i).Range("O1").Value
destFile.Range("D" & lrow + 1).Value = mybook.Worksheets(i).Range("O2").Value
'destFile.Range("E" & lrow + 1).Value = mybook.Worksheets(i).Range("O3").Value
End If
Next i
mybook.Close (False)
End If
Next FNum
destFile.Columns.AutoFit
End If
End Sub
Thanks
Bookmarks