Get most recent x months files, consolidate, pivot chart

[new58]

Looking to build a report that retrieves trailing 3 months weekly files (sum ~ 12 files) from a directory and consolidates to 1 workbook. The files are labled EX DATA mmddyy.xlsx The files being retrieved from the directory have 3 tabs, only want to use data from the first tab labeled 'NEGOTIATED', skip first row, and copy A:H, down to last value of column G (sometimes cells in columns A-F, H are blank). I also need to add band labels in column I that are based on $ value of column B.

Bands are:
<25,000
>25,000 - <50,000
>50,000-<100,000
> 100,000

Based on the bands, need to provide a pivot chart for sum of B data within each band and a pivot chart for count of B data in each band.

The code I have used before for grabbing the most recent file is a common one provided on the forum. Looking to learn how to adjust to grab trailing 3 months files, specify data retrieval, consolidate, label addition, and pivot chart in vba.


Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - Muni
Const myDir As String = "X:\000000\DATA"

'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile
Workbooks.Open myFolder & "\" & strFilename

Set FileSys = Nothing
Set myFolder = Nothing
End Sub