I saved myself about three or four hours of very tedious work today by writing a couple of simple Microsoft Excel VBA macros. When building very large rate or resource calculation CSV files ready for Deltek Cobra to load, there are some things I always end up doing that are really, really time consuming and unspeakably dull. Today, I finally got my finger out, and learned how to code a couple of simple macros that have saved me (and therefore my customer) hours, and will serve me again in the future.
I figured I’d share these macros as they aren’t too tricky to create or use and will doubtless save some of my fellow “Snake charmers” a lot of time and mind numbing tedium. I only haven’t tried this approach before because I was laboring under the belief that it would take longer to write a macro than just get on with the task.
However, staring down the barrel of several 1500 line resource calculation file builds kind of removed that excuse – a quick time/cost benefit analysis gave me all the gumption I needed. And as it turns out, Macros are pretty easy once you take the time to look into what’s involved.
Inserting Rows
I’ve used this macro for both building out a rate file with inflations and the accompanying resource calculation file.
Having been provided with a flat list of the resource names and their rates, I needed to insert seven rows between each name. Into these bank rows, I’ll be filling down formulae for calculating percentage rate increases, their dates and so on.
————————————————
Sub Insert_Rows()
Dim myRow As Long
Application.ScreenUpdating = False
For myRow = 1000 To 2 Step -1
Rows(myRow + 1 & “:” & myRow + 6).Insert
Next myRow
Application.ScreenUpdating = True
End Sub
—————————————————-
You’ll have to mess around with the values of the myRow variable in the Rows() line to change the number of rows that get inserted, but even so, it’s worth the time. Once this thing was running the way I wanted, I had all the blank rows I needed in under 20 seconds. In times past, I’d spend tedious and boring hours doing inserts with the mouse. On this job, I’m doing the biggest build out of rate and resource calculation files of my career, so the few minutes I invested in learning to make these macros work will save untold hours of time both now, and in the future.
For the resource calculations file, this copy and paste macro saved me further hours of torture. Once I’d set up all the results for one calculation at the top of the sheet, I wanted to copy that down to every seventh row thereafter.
>
Again, I usually do this by hand, but with files the size I’m dealing with, these macros are becoming a critical time saver.
———————————————————–
‘ Sub Paste_all_results()
‘
‘ Paste_all_results Macro
‘
‘ This macro will copy the range specified. It will then
‘ paste the range (in this case results and formula) to
‘ every 7th row in the third column (C).
‘
‘ Adjust the For loop range value to the first cell into
‘ which the first paste must go, and it will copy to every
‘ 7th row in that column thereafter.
‘ For x = To 1592
‘ With Worksheets(“RESOURCE_CALCULATIONS”)
‘ Range(“C2:S6”).Select
‘ Selection.Copy
‘ End With
‘
‘ With Worksheets(“RESOURCE_CALCULATIONS”)
‘ Cells(x, 3).Select
‘ ActiveSheet.Paste
‘ End With
‘ x = x + 6
‘
‘ Next x
‘
‘ End Sub
——————————-
Copy and paste these macros from this blog into your Macro command dialog and try them out for yourself; using a test copy of your file first of course. Now I’m no Macro expert, so if you have some more efficient ways to code these, please do share.
I wonder what other tedium I can relieve with excel macros?