Column AutoFill Macro

[jmich]

Hi anyone and everyone,

I am first time poster and serious novice when it comes to VBA. I am using Excel 2007. I am building a weekly cashflow model that details the cash flows for up to 30 different magazines in a publishing company. For each mag, I have built a model that uses assumptions in column D and then inputs the inflows and outflows into the appropriate week based on publication dates. Each mag uses rows 1 through 150 to account for the various costs, and each column represents one week. Currently, Columns E through BB are in use, which takes me through the end of the fiscal year.

I would like to create a macro that will take the formulas from the last used column and autofill the formulas into the next 13 empty columns (which would add one quarter to the projections).

Once I have the code for one worksheet, I would like to apply the same macro for all of the other worksheets in the workbook.

I have searched and searched these threads but I haven't been able to use the combination of answers to solve my specific problem. I am most grateful to anyone that can steer me in the right direction.

THANK YOU!

 

[jmich]

Just hoping some kind soul out there can give me a steer in the right direction.

Anybody up for the challenge???

 

[taigovinda]

Welcome to the board!

If your data is in A1:BB150, and you just want to copy over the last 13 columns to the right...

Sub AddThirteen()
For Each Sheet In Sheets
  Sheet.Range("BB1:BB150").Offset(0, -12).Resize(, 13).Copy Destination:=Sheet.Range("BC1")
Next Sheet
End Sub

Post back if that's not quite it

Tai

 

[jmich]

Thanks, Tai! I think you are almost there. Everything worked fine in terms of adding the 13 columns, carrying over the formulas, and applying the macro to each worksheet. The last bit that I need help on is that the last column will continue to change.

For example, currently 2 July 2010 is in last used column, BB. And when I run the macro, it adds 13 weeks, leaving 1 Oct 2010 in the last used column, BR. So that I can continue to add quarters, the Macro will need to recognise where the last used column is and then add the 13 as opposed to referring to BB each time.

Does that make sense?

Thanks so much for all the help so far. Very clean solution compared to some of the other things I was trying.

 

[taigovinda]

Hi Jmich, I figured that's probably what you wanted when you first asked, but was just too lazy to do it! ...maybe this:

Sub AddThirteen()
For Each Sheet In Sheets
  Sheet.Range("A1").End(xlToRight).Offset(0, -12).Resize(150, 13).Copy Destination:=Sheet.Range("A1").End(xlToRight).Offset(, 1)
Next Sheet
End Sub

Cheers,
Tai

 

[jmich]

Tai,

That worked a charm. I had to make a slight adjustment specific to my spreadsheet, but that means I'm finally learning a little bit about all of this VBA stuff.

Thanks for all your help!