Create new column with macro

[lordrummxx1]

Hey all,

I'm currently working on a private equity report that I want to automate. Ive made a rough draft of what I am looking to do below. Assume the first two columns are manual entry (Company Reporting, 6/30/2011). I want to be assign a macro to a button that, when pressed, will automatically add another column. The next column would have the =EOMONTH formula to get the correct date and the last line would have the =sum formula to get the total number above.

I know how to do this for one specific area, but I would like to be able to click the button, say on 12/31/2012, and the fields will extend off the 9/30/2011 column. Any advice?

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Company Reporting
[/TD]
[TD]6/30/2011[/TD]
[TD]9/30/2011[/TD]
[/TR]
[TR]
[TD]Fund Reporting[/TD]
[TD]3/31/2011[/TD]
[TD]6/30/2011[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Funds Invested
[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other Funds[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Contributions[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[JLGWhiz]

see if this is what you want. It assumes your data begins on row1, column1.

Sub t()
With ActiveSheet
    .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = Application.EoMonth(.Cells(1, Columns.Count).End(xlToLeft).Value, 3)
    .Cells(2, Columns.Count).End(xlToLeft).Offset(, 1) = Application.EoMonth(.Cells(2, Columns.Count).End(xlToLeft).Value, 3)
    .Cells(1, Columns.Count).End(xlToLeft).Offset(5).Formula = "=SUM(" & .Cells(1, Columns.Count).End(xlToLeft).Offset(3).Resize(2).Address & ")"
End With
End Sub

 

[lordrummxx1]

Thank you for the response! I tried it and it works. Do you know how I might be able to adjust the formula so that I can have the function begin in another cell?

 

[JLGWhiz]

The Cells(r, c) format has the Row number where for the 'r' and the Column number or letter for the 'c'. In most cases, I used the row number in the clear, but used relative references for the columns. The offsets are also based on the way the data was organized in the OP. (Row 3 was blank). If you can tell me where your data is located, I can modifiy the code for you, unless you want to try it yourself.

 

[JLGWhiz]

You would need to change the numbers in the six places in red font.

Sub t()
With ActiveSheet
    .Cells([COLOR=#ff0000]1[/COLOR], Columns.Count).End(xlToLeft).Offset(, 1) = Application.EoMonth(.Cells([COLOR=#ff0000]1[/COLOR], Columns.Count).End(xlToLeft).Value, 3)
    .Cells([COLOR=#ff0000]2[/COLOR], Columns.Count).End(xlToLeft).Offset(, 1) = Application.EoMonth(.Cells([COLOR=#ff0000]2[/COLOR], Columns.Count).End(xlToLeft).Value, 3)
    .Cells([COLOR=#ff0000]1[/COLOR], Columns.Count).End(xlToLeft).Offset(5).Formula = "=SUM(" & .Cells([COLOR=#ff0000]1[/COLOR], Columns.Count).End(xlToLeft).Offset(3).Resize(2).Address & ")"
End With

End Sub