Solved: Button Command Challenging

[ngdaug]

I have a pretty simple worksheet (see attached) where a user will type in a code, and using an array formula the line is copied onto another tab that keeps track of that specific account. For example, a user will enter G for Gas and then the date, description, and amount spent, is copied over to the "Gas" tab. Here is where I need help and am failing miserably. At the end of each month, a user goes through each tab and has to do the following:
1.) Find the last entry for that month and bold the total and write the month beside the total.
2.) Underneath the total erase the formula (its an if statment) and put in the array so that next months numbers don't add on to the previous month's numbers. (But is just done for the cell directly below last month's total I need the following cells to use the if statement.)

While it doesn't seem like much, the real spreadsheet has quite a few tabs. Can anyone help me create some VBA code so that when I execute the macro, it does that automatically for each tab?

Thanks for all your help and if this is confusing please let me know and I'll make it clearer.

Cheers,

Nate

[nst1107]

Welcome to the forum. Good explanation of your objective. It makes sense. I'm working on it. One question: Is there a reason why you use an array formula underneath the bolded totals instead of just using a simpler formula like "=IF(A11="","",C11)"?

[nst1107]

Here's what I've got:[VBA]Option Explicit
Dim Memory1 As Integer
Dim Memory2 As Long
Dim flag As Boolean
Sub PrepareForNextMonth()
Dim ws As Worksheet
Dim i As Integer, j As Integer
Dim Ends
For i = 2 To Worksheets.Count
Set ws = Sheets(i)
ReDim Ends(0)
Memory1 = Month(ws.Cells(3, 1))
Memory2 = 3
Do
If Not IsEmpty(Ends(0)) Then ReDim Preserve Ends(UBound(Ends) + 1)
Ends(UBound(Ends)) = EndOfMonth(ws)
Loop Until flag
For j = 0 To UBound(Ends)
With ws.Cells(Ends(j), 1)
.Offset(, 3).Font.Bold = True
.Offset(, 4) = Format(.Value, "mmm")
.Offset(, 4).Font.Bold = True
.Offset(1, 3).Formula = "=if(" & .Offset(1, 2).Address(False, False) & "="""",""""," & .Offset(1, 2).Address(False, False) & ")"
End With
Next
flag = False
Next
End Sub
Function EndOfMonth(ws As Worksheet) As Long
Dim c As Range
With ws
For Each c In .Range("A" & Memory2 + 1 & ":A" & .Columns("A").End(xlDown).Row)
If c = vbNullString Then
EndOfMonth = Memory2
flag = True
Exit Function
End If
Select Case Month(c)
Case Memory1
Memory1 = Month(c)
Memory2 = c.Row
Case Is > Memory1
EndOfMonth = Memory2
Memory1 = Month(c)
Memory2 = c.Row
Exit Function
End Select
Next
End With
End Function
[/VBA]
A few things:
1.) Since I didn't hear from you before I finished this, instead of inserting an array formula beneath the total, the procedure inserts the simpler IF() statement.
2.) This will not behave well when multiple years are involved in the same workbook. (Since the example you gave is titled "2008.xls", I assumed you had a seperate workbook for each year. Plus, it was easier on me that way. )
3.) It will bold the total, insert the formula, etc. for the last entry on each sheet it looks at. So, just be aware that running this before you've entered the last of the data for your month will mess you up.

[ngdaug]

Nate thanks so much I really appreciate your help. The macro works excellent and there is plenty of variability and flexibility to make it work with some of my abnormal system necesities. Thanks again,

Nate

PS (Love the name.)

[nst1107]

You're welcome for the help. To help keep the board clean, when you get the answers you need, mark your thread as solved by clicking "Thread Tools" near the top of the page.

Ditto on the name.