Extract mileage from previous month's spreadsheet

[garrypsmith]

Hi,

I am a complete novice.

I have a macro enabled Excel template called 'Expense Form', which currently contains a macro that increments the statement number by 1 each time it's opened (I've managed to find appropriate coding for this).

I would now like to add a macro that extracts the previous month's closing mileage, therefore making it the current month's opening mileage but have no idea about the coding.

Obviously I'm working on the July spreadsheet (the previous was saved as Jun '23.xlsm) and the cell I need to extract from Jun spreadsheet is "D42"

Any suggestion would be greatly appreciated.

Thanks,
Garry

 

[Herakles]

How many worksheets does the Template workbook have and is the code that increments the statement number by 1 in this workbook?

 

[garrypsmith]

How many worksheets does the Template workbook have and is the code that increments the statement number by 1 in this workbook?

Hi, thanks for your response.

I have a folder called Expenses. This contains the template workbook (saved as Expense Form (Test)1.xltm) and has 1 worksheet in it. This worksheet was initially saved as Expense Form and contains the macro to increment the statement number by 1, using the following code (I've added another column, hence the cell reference changing from my original question).

Private Sub Workbook_Open()
Range("L2").Value = Range("L2").Value + 1
End Sub

As this was the initial template, it saved as Expense Form (Test)1.xlsm and this was opened to commence the series, subsequently saving as May '23.xlsm in the Expenses folder (I guess I could rename it to 052023.xlsm). For June, I opened May.xlsm and so on, the statement number increased by 1 each time as required.

Simple answer to your question, there's only 1 worksheet per workbook.

 

[Herakles]

Place this code in the Workbook_Open code module of the workbook that has the template worksheet in it.

Open this workbook at the start of the month that you need a new expenses workbook for.

It will get the closing mileage for the previous month, copy the Expenses worksheet in the template to a new workbook for the
current month and name it accordingly.

It puts the opening balance in cell A1 but you will need to change this line to state where you want it to go.

.Sheets(1).Range("$A$1").Value = dblClosing

Regarding the incrementing of the statement number. I think that this should be calculated from the month of the first statement as
this means that it will always be correct. What year and month did you start creating these statements?

You will need to change this line as appropriate:

strFolder = "C:\Dump\Expenses\"

Test it on a copy of your folder and files

Private Sub Workbook_Open()
Dim dteDate As Date
Dim strFolder As String
Dim strFilename As String
Dim dblClosing As Double
Dim Wb As Workbook
Dim WbThisMonth As Workbook
Dim WbNextMonth As Workbook

    If MsgBox("Do you want to create this months mileage workbook?", vbYesNo, "Expenses") = vbNo Then
        Exit Sub
    End If
        
        For Each Wb In Workbooks
            If Wb.Name <> ThisWorkbook.Name Then
                Wb.Close savechanges:=True
            End If
        Next Wb
        
        strFolder = "C:\Dump\Expenses\"
                
        ' Get closing mileage from last months expenses file.
        
        dteDate = DateSerial(Year(Date), Month(Date) - 1, 1)
        
        strFilename = strFolder & Format(dteDate, "MMM") & " '" & Format(dteDate, "YY") & ".xlsx"
        
        If Dir(strFilename) <> "" Then
            Set WbThisMonth = Workbooks.Open(strFilename)
            dblClosing = ActiveWorkbook.Sheets("Expenses").Range("$D$42")
            ActiveWorkbook.Close savechanges:=False
        Else
            MsgBox "Previous months expenses file is missing.", vbInformation, "Warning!"
            Exit Sub
        End If
                       
        ' Create this months expenses file and set the opening mileage.
        dteDate = DateSerial(Year(Date), Month(Date), 1)
        
        strFilename = strFolder & Format(dteDate, "MMM") & " '" & Format(dteDate, "YY") & ".xlsx"
            
        If Dir(strFilename) = "" Then
            Worksheets("Expenses").Copy
            With ActiveWorkbook
                .Sheets(1).Range("$A$1").Value = dblClosing
                .SaveAs strFilename
                .Close
            End With
        Else
            
            MsgBox "Expenses workbook " & strFilename & " already created." & vbCrLf & _
                "The opening balance may not be correct. Adjust manually", vbInformation, "Warning!"
        
        End If

End Sub

 

[garrypsmith]

Place this code in the Workbook_Open code module of the workbook that has the template worksheet in it.

Open this workbook at the start of the month that you need a new expenses workbook for.

It will get the closing mileage for the previous month, copy the Expenses worksheet in the template to a new workbook for the
current month and name it accordingly.

It puts the opening balance in cell A1 but you will need to change this line to state where you want it to go.

.Sheets(1).Range("$A$1").Value = dblClosing

Regarding the incrementing of the statement number. I think that this should be calculated from the month of the first statement as
this means that it will always be correct. What year and month did you start creating these statements?

You will need to change this line as appropriate:

strFolder = "C:\Dump\Expenses\"

Test it on a copy of your folder and files

Private Sub Workbook_Open()
Dim dteDate As Date
Dim strFolder As String
Dim strFilename As String
Dim dblClosing As Double
Dim Wb As Workbook
Dim WbThisMonth As Workbook
Dim WbNextMonth As Workbook

    If MsgBox("Do you want to create this months mileage workbook?", vbYesNo, "Expenses") = vbNo Then
        Exit Sub
    End If
       
        For Each Wb In Workbooks
            If Wb.Name <> ThisWorkbook.Name Then
                Wb.Close savechanges:=True
            End If
        Next Wb
       
        strFolder = "C:\Dump\Expenses\"
               
        ' Get closing mileage from last months expenses file.
       
        dteDate = DateSerial(Year(Date), Month(Date) - 1, 1)
       
        strFilename = strFolder & Format(dteDate, "MMM") & " '" & Format(dteDate, "YY") & ".xlsx"
       
        If Dir(strFilename) <> "" Then
            Set WbThisMonth = Workbooks.Open(strFilename)
            dblClosing = ActiveWorkbook.Sheets("Expenses").Range("$D$42")
            ActiveWorkbook.Close savechanges:=False
        Else
            MsgBox "Previous months expenses file is missing.", vbInformation, "Warning!"
            Exit Sub
        End If
                      
        ' Create this months expenses file and set the opening mileage.
        dteDate = DateSerial(Year(Date), Month(Date), 1)
       
        strFilename = strFolder & Format(dteDate, "MMM") & " '" & Format(dteDate, "YY") & ".xlsx"
           
        If Dir(strFilename) = "" Then
            Worksheets("Expenses").Copy
            With ActiveWorkbook
                .Sheets(1).Range("$A$1").Value = dblClosing
                .SaveAs strFilename
                .Close
            End With
        Else
           
            MsgBox "Expenses workbook " & strFilename & " already created." & vbCrLf & _
                "The opening balance may not be correct. Adjust manually", vbInformation, "Warning!"
       
        End If

End Sub

Thank you for the coding.

Unfortunately I don't completely understand the process, as I said at the start of the thread, I'm a complete novice.

I've changed in the template and saved as a .xltm file.

I've also created a start month Apr '23 and put the 'start' mileage (i.e. closing mileage from Mar '23) in D43

That's where I get confused, however, I will continue to persevere with your coding until I get there. Thanks for your help