Forum Discussion
using VBA to copy formulas down as well as across and keeping the ranges variable.
sbast22 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.
- 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.
sbast22 Can you upload a file with some made-up data for three or four projects and visualise what you want to achieve.
Riny_van_Eekelen 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 & 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 :-/.
