Selected Answer
Martin
Here's an approach which should work, assuming you have a workbook for a single year (as you say).
In the revised code and file below (changes in bold), I add a new variable for the summary expenses "yearSheet" and set it to Monthly. A couple of other variables are added for row and column numbers.
Then, BEFORE your code transfers values to the General Ledger sheet, thereis a check that the corrrect year is being used in the form then three steps to add the value into Monthly. In each case a failure will give an error mesage suggesting an action and exit the sub, leaving the form data in place (without transferring the data to the General Ledger sheet).
In the first, it compares the entered month (converted to lowercase) with the the first 3 characters of the full months listed in row 3 of Monthly (agin in lowercase) and finds the matching column number.
In the second step. it loops down column B matching the entered Category (and returns the row it makes a match). I thought that the error MsgBox probably wasn't needed but it showed that the Category Sub-contracts appears in your form (/sheet SOURCE) but not in Monthly.
That locates the row and column for the value and the third step just adds the value from Data Input to whatever is already there.
On Data Input form, I notice that you're not doing any rounding of gross values (even though your custom cell formatting might make you think the number is just two decimal places) so have unnecessary decimal places will appear in General Ledger and might produce summing errors later. To fix that, I changed the formula in Cell I11 (FIELDSVALUE) to:
=I9-ROUNDUP((I9/(1+E11)),2)
where the ROUNDUP will round the tax portion up to two decimal places i.e. the nearest cent (for dollars).
Here's the revised code, changes in bold:
Sub Store_Data()
' TeachExcel.com
' Takes data from one worksheet and stores in in the next empty row on another worksheet.
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim yearSheet As Worksheet, Mnth As Integer, Cat As Integer, n As Long 'variables for year's expenses etc
Dim nextRow As Integer
' Make some sheet variables so we can use those instead of hard-coding sheet references in the code.
Set sourceSheet = Sheets("Data Input")
Set dataSheet = Sheets("General Ledger")
Set yearSheet = Sheets("Monthly")
' Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row
' ### Copy gross value to the year sheet ###
'First check correct year
If sourceSheet.Range("F5").Value <> 2021 Then
MsgBox "Stated year is not covered by this workbook, please check then re-enter form"
Exit Sub
End If
'1. Find month column
For n = 6 To 50
If LCase(Left(yearSheet.Cells(3, n), 3)) = LCase(sourceSheet.Range("D5").Value) Then Mnth = n 'use lower case of Month (c.f. full)
Next n
If Mnth = 0 Then
MsgBox "Month not found on Monthly sheet, please check then re-enter form"
End If
'2. Find category row
For n = 3 To 60
If yearSheet.Cells(n, 2).Value = sourceSheet.Range("I15").Value Then Cat = n 'Income/Expense Category
Next n
If Cat = 0 Then
MsgBox "Category not found on Monthly sheet, please check, correct and re-enter form"
Exit Sub
End If
' 3. add gross value to what's there
yearSheet.Cells(Cat, Mnth) = yearSheet.Cells(Cat, Mnth) + sourceSheet.Range("I13").Value 'Nett value
MsgBox sourceSheet.Range("I15").Value & " value transferred to sheet: " & yearSheet.Name ' this could replace Success msg in Validate sub
' Input the form values into the Data sheet.
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("D5").Value
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("E5").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("I7").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("I9").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("I11").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("I13").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("I15").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("I17").Value
'Clear Data
sourceSheet.Range("I7").Value = ""
sourceSheet.Range("I9").Value = ""
sourceSheet.Range("I15").Value = ""
sourceSheet.Range("I17").Value = ""
End Sub
You can improve it by finding last row and column for the n= find loops (instead of the nominal upper limits I set) if you like.
Hope this helps.
