Help with Macro to Insert a New Column to the Left of Moving Column

[Flash311]

Hi, this is my first post. I'm only a novice with VBA and creating macros. I try to stick the basics and recording. I usually do a pretty good job of figuring it out on my own through google and this forum, but I haven't been able to find any help with this latest macro I'm trying to make. I'm hoping someone here might be able to help. I've created a workbook to track construction estimates and spending. I have an 'Estimates' worksheet which contains all the work items within a contract in the rows, and the columns contain the individual estimates (Est. 1, Est. 2, etc.). After the last estimate, I have a 'Contract Total' column which just sum's up the row for each item. When I want to add in the costs for the latest estimate, I would just manually insert a new column to the left of the 'Contract Total' column, then autofill everything from the previous estimate to the right.

I'm trying to create a macro to do this all automatically so that I can give the workbook file to another employee that isn't very well versed with Excel to track his own contracts. Here's what I was able to come up with through recording a macro:

Sub InsertNewEstimate()
Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("C3:C3").Select
Selection.AutoFill Destination:=Range("C3:D3"), Type:=xlFillDefault
Range("C3:D3").Select
End Sub

The problem with it is the column needs to be variable, not set like above. Instead of always inserting the new column at D and moving the rest of the columns to the right, I want it to insert the new column at D for Est. 2, E for Est. 3, F for Est. 4, etc.

Sorry for the long post, I'm just trying to make myself clear. Hopefully I didn't make it out to be more confusing. Any help would be greatly appreciated.

Thanks,
Aaron

 

[mwrohr]

If the column is going to be variable, use the .Find function to find the column you want and then continue with the insert

Dim r as range

Set r = Sheet1.UsedRange.Find("Column Header", , , xlWhole)

r.entirecolumn.insert(xltoleft)

 

[Flash311]

Thanks, that worked to get the column inserted in the correct location, but I can't figure out how to get the autofill to work now. The only thing I want to autofill is "Est. #" at the top of the column, so if you're inserting Estimate 2, the macro will insert the column and display "Est. 2" in the first row, then the next time the macro is run, it will show Est. 3.

 

[mwrohr]

How or what determines the Estimate #? Is this something defined by the user?

 

[Flash311]

It's only sequential, the contract starts with Estimate 1, and goes up from there. The way I envision the sheet working is giving it to someone at the start of a contract with only an Est. 1 column, after estimate 1 is entered, they would run the macro, it would insert the column for the next estimate and auto fill the heading Est. 1 from C3 to D3, to say Est. 2. The next run of the macro would insert the column and then autofill Est. 2 to Est. 3 from D3 to E3 and so forth.