Macro to add Multiple rows in same sheet

[qbzee007]

Hi All,

I have been using the basic functions for excel for years now and have only recently needed to work with Macros.

The basic use of the macro will be to add additional products onto an income statement.

For example: In a sheet i will have revenue for each of the individual products as well as costs for the individual products. What i am trying to do is to automatically add a new product into the income statement
I tried to record a macro but it will work in the first instance but the minute i add the second product it will work with the revenue but the costs are not in the same order. I think this is because it fixed the row where i enter the new row which is fine for the revenue as it is at the top but does not work with cost. Here is how is looks

Orginal:

A1:Revenue
A2: Apples
A3: Oranges
A4: Bananas
A5:Costs
A6: Apples
A7: Oranges
A8: Bananas

After running the macro I recorded i get

A1:Revenue
A2:Pears
A3:Apples
A4: Oranges
A5: Bananas
A6:Costs
A7: Pears
A8: Apples
A9: Oranges
A10: Bananas


This is fine.

The problem occurs when i then add the second new product
A1:REVENUE
A2:Paw Paw
A3:Pears
A4:Apples
A5: Oranges
A6: Bananas
A7:Paw Paw (this is supposed to be under costs)
A8:COSTS
A9: Pears
A10: Apples
A11: Oranges
A12: Bananas


As you can see once i add the second new product it gets inserted above costs and it should be below. So basically i need a evolving macro which is able to do this. Note that this is a basic example as i will be having different kind of costs for each of the products. For example cost will be spilt into fixed and variable costs and this is all calculated in one sheet

Your assistance will be appreaciated

[stnkynts]

We are probably going to need you to submit a copy of your workbook. It sounds like there are so many potential factors, that aren't answered in your post, that any macro would probably be doomed to fail. BTW, the reason your recorded macro didn't work the second time is because it is referencing static ranges (example: A2, A7, etc) rather than dynamic ones.

[qbzee007]

Due to the sensitivity of the information i will not be able to submit my workbook hence i chose to use the example of the fruit store.
How would dynamic ranges work in this particular example?

[stnkynts]

How would dynamic ranges work in this particular example?

That's a question with a lot of different, yet all correct, answers. It really depends on the layout of your workbook.

I was thinking it might be best to use the find method but it depends on a couple of factors.

I could write you a macro that will work great for your example but I highly doubt it would work the way you want on your workbook. I wish you the best of luck.