Hi there!
I have a working macro that works fine when transferring data from current month. However, when for example it is october tomorrow and I want to transfer september data it goes wrong. Every week as new file gets placed inside a folder with the most up-to-date interest rates. So for example when I log in on the 3rd of october the october file will not be present, instead I want to transfer the september latest file interest rates.
The folders are all called like this : 1. January, 2. February, 3. March which goes on till december. They are all seperate folders being placed in this path G:\Companyname\Finance Planning & Control\Finance en Reporting\2022\Balans\8. Langlopende schulden.
Currently what it is doing is this: I have a macro which opens the latest file present in current months folder and transfers Cells 41 till 47 (interest rates) with latest column into my current workbook and then closes the other workbook. What goes wrong however is what I mentioned in the opening phrase. Whenever I have a new month and I need to transfer something of the previous month (latest document) I do not know what to rephrase in my macro.
The macro can be seen below (everything works fine) except that I only assigned a variable to current month. I can assign one for previous month but then I am stuck. Any assistance is appreciated, many thanks!
I have a working macro that works fine when transferring data from current month. However, when for example it is october tomorrow and I want to transfer september data it goes wrong. Every week as new file gets placed inside a folder with the most up-to-date interest rates. So for example when I log in on the 3rd of october the october file will not be present, instead I want to transfer the september latest file interest rates.
The folders are all called like this : 1. January, 2. February, 3. March which goes on till december. They are all seperate folders being placed in this path G:\Companyname\Finance Planning & Control\Finance en Reporting\2022\Balans\8. Langlopende schulden.
Currently what it is doing is this: I have a macro which opens the latest file present in current months folder and transfers Cells 41 till 47 (interest rates) with latest column into my current workbook and then closes the other workbook. What goes wrong however is what I mentioned in the opening phrase. Whenever I have a new month and I need to transfer something of the previous month (latest document) I do not know what to rephrase in my macro.
The macro can be seen below (everything works fine) except that I only assigned a variable to current month. I can assign one for previous month but then I am stuck. Any assistance is appreciated, many thanks!
VBA Code:
Sub Rente_Tabel_Import()
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim wb As Workbook
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim LastRow As Long
Dim LastCol As Integer
Dim sMonth_Name As String
Huidig_Maand = MonthName(Month(Date))
sMonth_Name = Month(Date) & "." & " " & UCase(Left(MonthName(Month(Date)), 1)) & Mid(MonthName(Month(Date)), 2)
Huidig_Jaar = Year(Now())
PreviousMonday = Date - Weekday(Date - 1, vbMonday)
CurrentMonday = Date - Weekday(Date, vbMonday)
MyPath = "G:\Companyname\Finance Planning & Control\Finance en Reporting\" & Huidig_Jaar & "\Balans\8. Langlopende schulden\" & sMonth_Name & "\"
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
MyFile = Dir(MyPath & "ML Rentetabel" & "*.xlsx", vbNormal)
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
Do While Len(MyFile) > 0
LMD = FileDateTime(MyPath & MyFile)
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
MyFile = Dir
Loop
Set wb = Workbooks.Open(MyPath & LatestFile)
Set wsCopy = ActiveWorkbook.Worksheets("Rentetabel")
Set wsDest = Workbooks("Kostprijsmodel Eindversie V6").Worksheets("Default settings")
With ActiveSheet
LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
ColumnLetter = Replace(Cells(1, LastCol).Address(0, 0), 1, "")
End With
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "B").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
wsDest.Range("L2:L8").value = wsCopy.Range(Cells(41, LastCol), Cells(47, LastCol)).value
wb.Close
MsgBox "Rentebestand van" & " " & PreviousMonday & " " & "geimporteerd!"
End Sub
