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?

Company Reporting	6/30/2011	9/30/2011
Fund Reporting	3/31/2011	6/30/2011
Funds Invested	5
Other Funds	10
Total Contributions	15

<tbody>
</tbody>

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