This little tool seems to do all the right things. Just edit the macro to point the fPATH to the folder where all your deposit files will be stored.
The macro will create a deposit sheet for the date you name, then import all the files from the fPATH that start with that date.
Option Explicit
Sub ImportDeposits()
Dim MyDate As Date, wbDATA As Workbook, wsOUT As Worksheet
Dim CNT As Long, NR As Long, Increment As Boolean
Dim fPATH As String, fNAME As String, Accnt As Range
fPATH = "C:\Path\To\Import\Files\" 'remember the final \ in the folder string
If Environ("USERNAME") = "Jerry" Then fPATH = "C:\2016\Deposits\" 'for my testing, ignore this
MyDate = CDate(Application.InputBox("Enter the date to import deposits from:", "Import Date", Date, Type:=1))
If MyDate = "12:00:00 AM" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Sheets("DEPOSIT_SLIP_" & Format(MyDate, "MMM_DD")).Delete
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "DEPOSIT_SLIP_" & Format(MyDate, "MMM_DD")
Set wsOUT = ActiveSheet
With wsOUT.Range("A1:D1")
.Value = [{"Batch ID","Batch Total","Account_Number","Amount"}]
.Font.Bold = True
.Range("A2").Select
ActiveWindow.FreezePanes = True
End With
NR = 2
CNT = 1
fNAME = Dir(fPATH & Format(MyDate, "MMDDYYYY") & "*.xl*")
Do While Len(fNAME) > 0
Set wbDATA = Workbooks.Open(fPATH & fNAME)
With wbDATA.Sheets(1)
If WorksheetFunction.CountA(.Range("A19:A53")) > 0 Then
For Each Accnt In .Range("A19:A53").SpecialCells(xlConstants)
wsOUT.Range("A" & NR).Value = CNT
wsOUT.Range("B" & NR).Value = .Range("J55").Value
wsOUT.Range("C" & NR).Value = Accnt.Value
wsOUT.Range("D" & NR).Value = .Range("J" & Accnt.Row).Value
NR = NR + 1
Next Accnt
CNT = CNT + 1
End If
End With
wbDATA.Close False
fNAME = Dir
Loop
wsOUT.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
Bookmarks