using VBA to copy formulas down as well as across and keeping the ranges variable.

Question

Hello, I've got a large workbook of projects and several lines of funding for each project. at the base of each chunk of said project's funding there is a sum total. I'm wondering if it's possible to then drag each of those sum total formulas across the 3 columns to the right so that right next to the funding totals I can also total up some other datapoints. each project has different amounts of funding lines and so I need the formula to be smart enough to detect the sum totals to drag across and to apply this down the entire sheet of data. Is this possible and how can I achieve this?
Thank you in advance for all your help!

riny_van_eekelen · Answer

sbast22&nbsp;I may not have a clear picture in my mind of what you are dealing with, but is seems unwise to maintain a large list of funding information and then insert totals by project in the same list. Better to summarise the "funding data base" in a pivot table.

sbast22 · Answer

let's say that the totals to the right of each funding line is what's being billed against said project for billing period 1 ... then billing period 2 and so on. If I cannot figure out a way to sum across using vba then i have to manually do it to learn for each new billing period what the total billed will be. then verify that against my invoice.

riny_van_eekelen · Answer

sbast22&nbsp;Can you upload a file with some made-up data for three or four projects and visualise what you want to achieve.

sbast22 · Answer

Riny_van_Eekelen&nbsp;Please see file attached. This is much smaller scale as I mainly need to give a visual for how this works. Previously, we had to hand jam each total to be invoiced. But with this workbook I built we can use vlookup to line up the billing totals. The issue is that we get funding as we go and so the lines will change. Typically what I do is copy S4 &amp; T4 all the way down Then copy columns S over to column R as Values Only. From there, I basically drag the sum total formula across to column T. When doing this I also copy the blank cells in between because the spacing is basically the same in the workbook that I will then paste column R into once i've ensured that the bill looks accurate. I hope that's not too confusing :-/.

riny_van_eekelen · Answer

sbast22&nbsp;I took the liberty to restructure you data a bit, so that I could create a pivot table. See attached.&nbsp;Though, I realise that it has totally messed up your lookups, the point I would like to make is that you should not format raw data with merged cells, double header rows and empty columns. Keep it clean, so that you use pivot tables to the summarising for your.&nbsp;But then again, perhaps I totally misunderstand your needs. Then I can only apologise for having taken your time.

Forum Discussion

using VBA to copy formulas down as well as across and keeping the ranges variable.

6 Replies

Resources