Autofill last formula field for many columns at the same time

[emphase]

Hi,
I have a sheet with various monthly data time series in which each cell refers to another sheet linked with Bloomberg (automatic update).

When I want to update this sheet, I usually dragdown the last data formula in each column by one month as shown here:

(see red arrows)
http://www.emfin.com/dragdown.gif

However, doing this manually can take up to 1 hour with more than 400+ columns so I was wondering if I could have a macro that would do all this at once?

I found on another thread this macro:

Sub Fill_Lastrow()
Sheets("Sheet1").Range("A65536").End(xlUp).Select
Selection.AutoFill Destination:=Range("A" & ActiveCell.Row & ":A" & ActiveCell.Row + 1), Type:=xlFillDefault
End Sub

This macro above works but only for column A. However I have column A to OZ. How can I get the last data point of each column to be dragged down by 1 month and autofilled?

Can someone show me a modified version of the code above if possible so it works for many columns instead of just one?

Thanks alot for the help!

[Charles]

HI,


For each column do you have a formula for "Sum"?
Which version of excel are you using?

[emphase]

Hi Charles,
The formula in each column varies. Some columns are only used to take data from Bloomberg sheet so they are only =BloombergP115 for example.

Other columns are used to model those time series so they can contain various formulas like =SUMPRODUCT, =SUM, etc.

Basically, all I want is a drag down,i.e. select the last cell of 1 column, click the bottom right corner and drag down by 1 month and repeat for many columns.

I am using 2010.

[Charles]

emphase,

I'll see what I an do, but it's getting late and if I do not reply tonight I'll post some thing tomorrow. In the mean time another member may also answer this post.

I just looked at the code that I provided in another post and if you do a fill down it changes the value of the cell to the previous value + the fill down.
So let say the formula in column A is a sum and the value is "11" then the value in column A after the code will be "21". So this may be a problem with using the code provided.

[emphase]

Hi Charles,
Here is a simplified worksheet showing it better:

http://emfin.com/Autofill_Columns.xlsx

No problem, thanks alot!

[Charles]

emphase,

Thanks for the file. Now I see what you are doing and will try to have something for you tomorrow. The only other problem that may arise, unless some one knows of a faster method than the one I'm going to use is the program may run for some time before it finishes.

[emphase]

That's ok if it takes a while to perform the macro. 2-5min is certainly faster than an hour of manual updates for hundreds of columns. That's awesome!

[Charles]

emphase,

Question which row has headers from column A to the last column?

[emphase]

If by header, you mean description features in row 1 to row 7*. All columns have that same format of headers.

[Charles]

In your pdf row 7 appears to have "Headers" across all columns. Is this correct?

[emphase]

Yes this is correct, row 7 have a header across all columns.

[Charles]

emphase,

Ok this is what I came up with.
You may need to change the Sheet Name "DATA" to your actual sheet name.
If it's the same then ok. I have the code looking at Row 7 as the Header.
the code should go from column B to the last column in your data.
Copy and paste to a module. Test on a copy.

Please Login or Register  to view this content.

[Marcol]

Hmm?

Have you considered using a Table/List?

See here for more information
Overview of Excel tables

I can't vouch for your formulae, some/most will need revising to account for missing data.
I have done this in Sheet "Data" as best I can, and cut the workbook to 31/1/2000 for posting

With 31/01/1919 (31/1/2000 in the sample) in A8 then something like this in A9

Please Login or Register  to view this content.

Drag/Fill/Down

This will return the last day in each month,
Once set up the formula will be handled by the Table/List

[emphase]

Hi Charles,
Thank you so much, this is exactly what I was looking for. I work in finance so this is good for anyone modeling time series linked with Bloomberg.

Marcol, I will check your solution when I come back home.

[Charles]

emphase,

Thanks for letting me know and too if your satisfied please show the thread as "Solved".
And too if you do not mind click the scale below my name in the thread.