Filter month based on file name contains months

[desmortess]

Hi guys

I am looking to select a previous month excel file based on the file name which contains the month name from two folders.

First folder contains the file name as "File (HFS) Dec-2021" with month in the form mmm- year
Second folder contains the file name as "2021 12 rpt final" with year and month in the form of integer..

Can someone please help me in figuring out how to select the previous month file based on above two examples through vba and select the values from column A and save as new file??

Thank you so much

[Bob Phillips]

This shows you how to get/format the date



Debug.Print "File(HFS) " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yyyy")
Debug.Print Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy mm") & " rpt final"

[desmortess]

This shows you how to get/format the date



Debug.Print "File(HFS) " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yyyy")
Debug.Print Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy mm") & " rpt final"


Great, appreciate it ! This worked as expcted

[desmortess]

This shows you how to get/format the date



Debug.Print "File(HFS) " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yyyy")
Debug.Print Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy mm") & " rpt final"

Some reason I am getting an run-time error '9': Subscript out of range. But I cannot see any issues and debugging referring to this line in the code - Windows(FullFileName2).Activate


Sub test()
Dim FileName1 As String
Dim FileName2 As String
Dim PathName As String
Dim FullFileName1 As String
Dim FullFileName2 As String

PathName = "C:\Users\Excel File Extraction\"
FileName1 = Dir(PathName & "Copy (HFS) " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yyyy") & ".xlsx")
FileName2 = Dir(PathName & "Copy " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy mm") & " portfolio rpt final" & ".xlsx")
'FullFileName1 = PathName & FileName1
FullFileName2 = PathName & FileName2
'Workbooks.Open FileName:=FullFileName1
Workbooks.Open FileName:=FullFileName2
Windows(FullFileName2).Activate
Columns("B:B").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveSheet.Range("$B$1:$B$2123").AutoFilter Field:=1, Criteria1:="<>"
Windows("Test_Macro.xlsm").Activate
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Windows("Copy 2022 01 portfolio rpt final.xlsx").Activate
Range("B:B,J:J").Select
Range("J1").Activate
Selection.Copy
Windows("Test_Macro.xlsm").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

[snb]

Sub M_snb()
c00 = "J:\download\"
c01 = DateAdd("m", -2, Date)
c02 = Dir(c00 & "*" & StrConv(Format(c01, "mmmm-yyyy"), 3) & "*")
c03 = Dir(c00 & "*" & Format(c01, "yyyy mm") & "*")

With GetObject(c02)
.Sheets(1).UsedRange.Offset(, 1).Clear
.SaveAs "G:\OF\new.xlsx"
.Close 0
End With

With GetObject(c03)
.Sheets(1).UsedRange.Offset(, 1).Clear
.SaveAs "G:\OF\portfolio_new.xlsx"
.Close 0
End With
End Sub

[Bob Phillips]

Some reason I am getting an run-time error '9': Subscript out of range. But I cannot see any issues and debugging referring to this line in the code - Windows(FullFileName2).Activate


Try activating the workbook, not full path


Workbooks(FileName2).Activate

but it should be the active workbook after opening anyway. Better to set a workbook variable and use that to access the workbook


Dim wb As Workbook

PathName = "C:\Users\Excel File Extraction\"
FileName1 = Dir(PathName & "Copy (HFS) " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm-yyyy") & ".xlsx")
FileName2 = Dir(PathName & "Copy " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy mm") & " portfolio rpt final" & ".xlsx")
'FullFileName1 = PathName & FileName1
FullFileName2 = PathName & FileName2
'Workbooks.Open FileName:=FullFileName1
Set wsb = Workbooks.Open(FileName:=FullFileName2)
With wb.Worksheets(1)

.Columns("B:B").Autofilter
'etc.
End With