PasteSpecial Range class problem and inserting copied data

I am trying copy multiple rows of data and insert it between every existing row of data in my sheet.

You can see this in the attached sheet. Insert Row.xlsm

Row 3 and Row 4 should be copied and inserted between rows 5 and 6, 6 and 7, 7 and 8, etc.

This must copy the cell formulas not the values. It must also copy the whole row not just the first column.

I have tried the following code but it does not insert the actual formula it just inserts the values.

Please Login or Register  to view this content.

I have tried to modify the above as follows but it gives me the error "Unable to set the PasteSpecial Property of the Range class".

Please Login or Register  to view this content.

How can this be fixed to insert the copied rows with formulas not the values?

I have spent countless hours trying to fix the code but it does not seem to be helping.

If you can fix my code above or provide a totally different solution i would be very thankful!


Thanks

Ok so I got your suggestion to partially work. I changed the macro as below.

Note i changed line 3 and line 10.

Problem is the formula does not keeps its relative reference.
Now it seems to copy the formula but it literally copies exactly what the formula is. Which means that cell references are not correct. If the cell refers to L2 the formula copied will also contain L2.

I need the formula to change the reference so it matches with the row above it. Another words if the cell is inserted 10 rows below where it was copied it needs to reference L12 instead of L2.

That's the whole $ sign thing but in this case i don't have dollar signs and its still keeping the exact reference point.

Please help i think we are sooo close! Unless im missing something.

Please Login or Register  to view this content.