Handling Workbook Extensions

**Ascension** · 09-28-2010, 10:10 AM

Hello Forums,

This is my first post the Excel forums and marks my first serious foray into understanding more complicated excel functions. I haven't had formal training in excel outside of what we covered in my finance program, you could call me an above average user, but by no means an expert. I have created a worksheet that had a macro which copies and the inserts a replication of four columns in a workbook. Here is the pattern:
Title,Title,Title, Value of Cashflow
Input, Input, Input, Calculation
There are four sets of these cells, and then a sum of all the Calculation cells beneath the Value of Cashflow headings. My sum formula is hardcoded (i.e. =SUM(U4+Q4+M4+I4+E4)), so when i run the macro, the new Calculation isn't included. I figure some sort of lookup function matching against the "Value of Cashflow" heading is what is needed, but I can't figure out how to implement it. I assume i would be using an if statement? Any help would be great!

**royUK** · 09-28-2010, 10:11 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Ascension** · 09-28-2010, 10:28 AM

Here is a dummy workbook, hope it helps!

**Ascension** · 09-29-2010, 07:46 AM

Bump! Could really use a pointer on how to make this work!

**royUK** · 09-29-2010, 08:05 AM

Why are you extending horizontally?

**Ascension** · 09-29-2010, 08:37 AM

It's just the way it's set-up, it's a series of timed cashflows and timelines generally move horizontally left to right. Does that make programming it harder?

**royUK** · 09-29-2010, 08:53 AM

If it moved vertically it would be easier to read 7 you wouldn't need VBA

**Ascension** · 09-29-2010, 09:06 AM

Well if I wanted to maintain the structure of the page as horizontal, how can I accomplish this using VBA? Part of this is about learning how to do it, part of it is solving a task with given criteria, and one of those criteria is that the page needs to be horizontal. I'm all for shortcuts and ease of programming but, if your judge and user is an outsider you need to develop a product which makes intuitive sense to them. Given that, I need to figure out how to implement this and would love any help the more experienced excel users can give.