Macro - Create / Copy / Paste Formula every 5 rows for varying range

We have a file that exports to excel badly. We have the need to create a formula to copy A6 & B6 to K3 & L3, THEN copy this formula down K & L through the length of data on the current sheet.

The 2 problems are;

1. There are 5 blank rows between each formula

2. There is a varying length of data at each run - we can use column C to indicate length

Any advice would be greatly appreciated.

Thanks,

Jill

It would be easier if there was a sample workbook, but you could try this:

Formula:

Please Login or Register  to view this content.

Sorry, I have attached the spreadsheet. Values at A6 and B6 should be at K3 and L3. I need to copy this down through the end of the document - paste values - and, further sort and delete the rogue data in columns A & B.

Thanks,

Jill

Next time, don't attach a picture please. People have to recreate your scenario themselves.
Attach a workbook instead.

Please Login or Register  to view this content.

I guess I am not the only one re attachment.

Jolivanes,

Thanks, this works, though I wish I understood it better. Any thoughts about removing the leftover data in columns A & B?

Thanks Again,

Jill

I don't know if you want to delete the empty rows but if you do you could add this to the code.
Make sure you try it on a copy of your workbook in case it is not what you want.

Please Login or Register  to view this content.

Put this line between "Next i" and "End Sub"

My bad. I misread your requirements.
Try this instead. Again, on a copy of your workbook.
If you don't want the empties deleted, delete the line before "End Sub".

Please Login or Register  to view this content.

Awesome, that worked great! Though, now I have noticed that the values copied to columns K and L are all the same. There are values in Columns A and B for each row that contains data. What are your thoughts about this?

I appreciate your help with this.

--Jill

This works great. Thank you so much for your assistance!

That is a Cell Format problem Jill.
Try this. (On a copy of your WB again)

Please Login or Register  to view this content.

Thanks again!