copying rows and inserting them between correct dates

Hi, I'm making a budget in excel.
I have generated some dates that appear in column a. I want to make a macro that copies lines from sheet 2 and inserts them between the appropriate dates in column a sheet 1.

eg:
A
1/1/01
23/1/01
4/2/01
5/2/01
7/2/01

sheet2:
2/1/01
23/1/01
6/2/01

outcome:
A
1/1/01
2/1/01
23/1/01
23/1/01
4/2/01
5/2/01
6/2/01
7/2/01

the lines will also have 2 other fields for copying (description and amount).
I will also need to copy formulas to the inserted rows in some columns (eg balance) but not all (not the date column).

thanks

Hi assileM,

Try the attached file

thanks Jokacave

You are welcome, assileM

Is there a way to specify functions to be filled down each column even when rows are inserted?

What I am trying to do is have the balance column with a simple function: if column C is the amount and column d is the balance:

Please Login or Register  to view this content.

with this function copied down the whole column even when new rows are inserted using the macro you gave me.

I'm not sure how to do this because the macro copies the row to the bottom first (I think...) and then sorts it - so the formula would have to be copied once the sorting was finished...

I'm new to macros if you didn't notice

could this be done using the offset function?

Hi assileM,

Today it is too late.... I'll see what I can do to help you

ok i've fixed the balance problem using the offset function.

My excel sheet in progress is attached.

My big problem is that I thought the transactions from sheet 2 would be inserted into their places using the macro - but the macro puts them on the end of the list and then sorts. This ruins my generated dates - I was counting on the fact that if you insert a line, the generate date function doesn't take into acount that inserted line and still refers to the correct previous 2 dates. However, when you sort the new entries into their correct places these new dates ARE taken into account which screws up my generated date series.

Is there any way to get around this?

Thanks for your continued help!

Hi assileM,

If you want keep the formulas in column A I don.t see any way to solve the problem - and why u need the formulas in column A?

I want to keep the formulas to keep the dates I generated, and have amongst them the inserted dates from sheet 2.

Isn't there a way to use the vlookup function on each date in sheet 2 to find where it should be inserted; and then insert that row from sheet 2 into sheet 1 at the place found by vlookup?

Hi assileM,

If you want have only the dates and not the formulas I think the attached file will solve your problem.
Check it, please...

Sorry you misunderstood me.

I want to KEEP the date formulas in sheet 1, but have the transactions in sheet 2 inserted amongst the formula dates - the formula dates have to refer only to other formula dates - not the fixed inserted dates.

i want the macro to find where to insert the transaction and then insert it - not put it at the end and then sort.