Dec
15
Dim range1 As String ''Income
Dim range2 As String ''Charity
Dim range3 As String ''Saving
Dim range4 As String ''Housing
Dim range5 As String ''Util
Dim range6 As String ''Food
Dim range7 As String ''Transportation
Dim range8 As String ''Clothing
Dim range9 As String ''Medical
Dim range10 As String ''Personal
Dim range11 As String ''Entertainment
Dim range12 As String ''Debt
Private Sub SetupVars()
range1 = "<MONTH>!$<WEEK>$6" ''Income
range2 = "<MONTH>!$<WEEK>$8" ''Charity
range3 = "<MONTH>!$<WEEK>$11:$<WEEK>$13" ''Saving
range4 = "<MONTH>!$<WEEK>$16:$<WEEK>$23" ''Housing
range5 = "<MONTH>!$<WEEK>$26:$<WEEK>$33" ''Util
range6 = "<MONTH>!$<WEEK>$36:$<WEEK>$37" ''Food
range7 = "<MONTH>!$<WEEK>$40:$<WEEK>$46" ''Transportation
range8 = "<MONTH>!$<WEEK>$49:$<WEEK>$51" ''Clothing
range9 = "<MONTH>!$<WEEK>$54:$<WEEK>$59" ''Medical
range10 = "<MONTH>!$<WEEK>$62:$<WEEK>$79" ''Personal
range11 = "<MONTH>!$<WEEK>$82:$<WEEK>$83" ''Entertainment
range12 = "<MONTH>!$<WEEK>$86:$<WEEK>$105" ''Debt
End Sub
Private Function GetRange(week As Integer, range As Integer, month As String) As String
''Weekly Columns are 1=D, 2=H, 3=L,4=P, 5=T
Dim weekColumn As String
Dim strRange As String
Dim tempRange As String
weekColumn = Switch(week = 1, "D", week = 2, "H", week = 3, "L", week = 4, "P", week = 5, "T")
strRange = Switch(range = 1, range1, _
range = 2, range2, _
range = 3, range3, _
range = 4, range4, _
range = 5, range5, _
range = 6, range6, _
range = 7, range7, _
range = 8, range8, _
range = 9, range9, _
range = 10, range10, _
range = 11, range11, _
range = 12, range12)
tempRange = Replace(strRange, "<MONTH>", month)
tempRange = Replace(tempRange, "<WEEK>", weekColumn)
GetRange = tempRange
End Function
Private Function GetPreviousMonthFromMonth(month As String)
GetPreviousMonthFromMonth = Switch(month = "Jan", "Jan", _
month = "Feb", "Jan", _
month = "Mar", "Feb", _
month = "Apr", "Mar", _
month = "May", "Apr", _
month = "Jun", "May", _
month = "Jul", "Jun", _
month = "Aug", "Jul", _
month = "Sep", "Aug", _
month = "Oct", "Sep", _
month = "Nov", "Oct", _
month = "Dec", "Nov")
End Function
Private Function ConfirmCopy() As Boolean
Dim result As VbMsgBoxResult
result = MsgBox("Copying will replace any vaules you have already entered with those from last month" _
& vbCr & "Do you want to do this?", vbYesNo, "Replace this Month's Data?")
If result = vbNo Then
ConfirmCopy = False
Else
ConfirmCopy = True
End If
End Function
Public Sub CopyMonth()
''Make sure they want to copy and did not
''click by accident
Dim confirm As Boolean
confirm = ConfirmCopy
If confirm = False Then Exit Sub
Dim previousMonth As String
Dim thisMonth As String
Dim lastMonthRange As String
Dim thisMonthRange As String
Dim weekNumber As Integer
Dim rangeNumber As Integer
''Call to initialize variables
SetupVars
''Get the Month names involved in the copy
thisMonth = ActiveSheet.Name
previousMonth = GetPreviousMonthFromMonth(thisMonth)
''There are 48 ranges on each sheet that need to be copied, thats 96 total ranges
For weekNumber = 1 To 5
For rangeNumber = 1 To 12
''Get Range as a string
lastMonthRange = GetRange(weekNumber, rangeNumber, previousMonth)
thisMonthRange = GetRange(weekNumber, rangeNumber, thisMonth)
range(lastMonthRange).Copy
range(thisMonthRange).PasteSpecial xlPasteValues
range(thisMonthRange).NumberFormat = "$#,##0_);($#,##0)"
Next rangeNumber
Next weekNumber
''move the selection back to the top of the sheet
range("A1").Show
range("A1").Select
End Sub
Ok so I have used my last budget spreadsheet for a year now and I had a few things that I wanted to change. Here they are. I won’t bother explaining Dave’s budget form, if you have not read my first post on this, please read the post My First
do I have to enter this macro code in somewhere? do I copy/paste it? If so, where? I would like to use your version of dave’s spreadsheet, and want it to be streamlined to work great…just want to know how to set it all up. Thanks, ~Rusty
I actually have the same type question as Rusty….How do you implement the macro with your spreadsheet? Thanks, Richard
Mt question is exactly the same. Do I need to do anything special or just click enable macros?
Thanks, this is going to be very helpful.
I am very excited to find this as I was looking for a Dave Ramsey spreadsheet to use. I am not experienced with macros, and do not know how important this will be to the use of the spreadsheet. I am willing to use these, but like the others… I don’t know how. I am on wk 4 of FPU.
All
The macro code is only there for other nerds
to see how the spreadsheet works.
You do not have to enter anything into the spreadsheet. The code is already there.
Chris
WHAT?! I don’t know a thing about macros. I’ll ask my husband, but although he loves Dave Ramsey, i can’t seem to get him to a proper budget.
Is it possible to get the password to investigate the VBA myself? Thanks!
The current version of the spreadsheet does not have a password, so you can edit it all you want.
Chris, this is great! I love excel and dabble in VBA (although not enough to do any of what you’ve done!). However, my wife has a Mac at home and that is our main home computer. The copy button doesn’t appear to work (get the debug on the first confirmation pop-up box…). Don’t suppose you know how to make the macros work on on the Mac version of Excel?
Even if we can’t use the copy button, I like your version better than the plain vanilla spreadsheet. We’re in week 4 of FPU and am excited to get on a budget for the first time in our 12 year marriage!
Unfortunately Microsoft did not put VBA in the Mac version of MS Office, so the macro will not work on the Mac. Maybe someday?
great pread sheet i love it just one problem i have i change some of the headings to fit my budget but when going into next month it does not keep i am a nerd but not a good computer nerd is ther any way of keeping my changes from month to month with out retyping it
To all the previously mentioned commentors. Blogs could be significantly better to learn should you can maintain Your comments very simple and to the stage. No-one likes to study large comments once the concept could be conveyed using a not as long remark
Chris,
I have been using your ASP(Allocated Spending Plan)spreadsheet for about a year and a half and I love it. My wife even loves it, and she is not and Excel geek at all. Last month I changed a few line items to make the sheet more personalized and I messed up the “copy” macro. If I send you my file would you be able to help me fix it? I tried the macro tutorial in excel 2007 but it didn’t work
We went through FPU 2 years ago and are now coordinating FPU in our church. In 2007 we had over $100k in consumer debt. In December 2011 we will finally be DEBT FREE!! and have our Baby Step 3 complete!