I have an excel file with the numbers in column C that represents the part of the filenames. The macro looks the cells in this column opens the directory where the current file is placed and looking for files with this part of the name as in cell. The code works perfectly with the eception of openning the file that contains number 2020. It skips only the file with number 2020. Is something have to do with the date from the system?
I am using following code:
I am using following code:
VBA Code:
Dim cell3 As Range
Dim FileName As String
Dim CellName As String
Dim Fpath As String
Dim wb As Workbook
For Each cell3 In ActiveSheet.Range("C3:C" & Cells(Rows.Count, 3).End(xlUp).row)
eColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
If eColumn >= 1 Then eColumn = eColumn + 1
Fpath = ThisWorkbook.Path & "\"
CellName = cell3.Value
FileName = Dir(Fpath & "\*" & CellName & "*.xlsx")
Select Case True
Case Trim(cell3.Value) <> "" And Right(FileName, 12) Like "*" & CellName & "*"
Workbooks.Open FileName:=Fpath & FileName
Set wb = ActiveWorkbook
Range("B1:B6").Select
Selection.Copy
Windows("PROBE.xlsm").Activate
ActiveSheet.Range(Cells(cell3.row, eColumn), Cells(cell3.row, eColumn)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Skipblanks:=False, Transpose:=True
Application.CutCopyMode = False
Application.DisplayAlerts = False
wb.Close
End Select
Next cell3