I created a code to open the most recent file in a specific folder and copy the value if a specific cell to another in a different excel file, the program executes everything, including I receive the value "msgbox" but the values are not written on the second sheet " EEC QEC.xlsx ", could someone help me?
VBA Code:
Public Sub recentFilesSpecificFolder()
Dim myFile As String, myRecentFile As String, fileExtension As String
Dim recentDate As Date
myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\Andre\EEC\QE"
fileExtension = "*.xls"
If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"
myFile = Dir(myDirectory & fileExtension)
If myFile <> "" Then
myRecentFile = myFile
recentDate = FileDateTime(myDirectory & myFile)
Do While myFile <> ""
If FileDateTime(myDirectory & myFile) > recentDate Then
myRecentFile = myFile
recentDate = FileDateTime(myDirectory & myFile)
End If
myFile = Dir
Loop
End If
myMostRecentFile = myRecentFile
MsgBox "Path: " & myDirectory & vbCrLf & "File: " & myMostRecentFile
Workbooks.Open Filename:=myDirectory & myMostRecentFile
Call WriteValues
End Sub
Public Sub AutoRunMacro()
Call recentFilesSpecificFolder
End Sub
Private Sub KillEmpty()
Dim k As Long
Workbooks.Open Filename:=Environ("userprofile") & "\Documents\Projeto_Luis\EEC QEC.xlsx"
ws2 = ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets("2019").Select
Range("H141").Select
k = ActiveSheet.Range("H141", ActiveSheet.Range("H141").End(xlDown)).Rows.Count
If ActiveCell.Value = "" Then
Call FillCells
ElseIf ActiveCell.Value <> "" Then
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
Call FillCells
End If
End Sub
Private Sub FillCells()
Set sh = ActiveSheet
For counter = 1 To ws
For counter2 = 1 To ws2
If sh.Name <> "2019" And sh.Name <> "2020" And sh.Name <> "2021" And sh.Name <> "Total" And sh.Name <> "Iluminação Exterior" Then
Workbooks(myDirectory & myMostRecentFile).Worksheets(counter).Range("W110").Copy _
Workbooks("EEC QEC.xlsm").Worksheets("counter2").Range("H141")
Workbooks(myDirectory & myMostRecentFile).Worksheets(counter).Range("AI110").Copy _
Workbooks("EEC QEC.xlsm").Worksheets("counter2").Range("I141")
End If
Next counter2
Next counter
MsgBox "ola"
End Sub