vba to create 2 rows of data from 1 row

Hi,

I am trying to extract certain columns from a row to another row in anothe worksheet, which is working.

so data looks like this.

aaaa bbbb date figure 1 figure 2 mmmmm xxxxx yyyyy

extracted data should look like this

aaaa date figure 1 mmmmm xxxxx
bbbb date figure 2 mmmmm yyyyy

If I only have one original line my code worked perfectly, However, when I have say 4 original rows the 1st line of extracted data comes out ok but the second line does not.
I am using R1C1 formula filling down to extract the data. which is ok if there is only I line cause the formula would be R1C1 for extract 1, and R-1C1 for extract 2. But if I have the additional lines the 2nd extract pulls nothing as it is pointing to the wrong place.

Here is the code I have sofar based around a loop

Please Login or Register  to view this content.

Can anyone assist me with this as I am really stumped?
Thanks
Andy

Hi, Andy.
Try replacing this piece of the second code you posted above

Please Login or Register  to view this content.

By this

Please Login or Register  to view this content.

This will continue inserting formulae, tell us if you prefer the values instead.

What I meant is: your code pulls some information from a sheet and put it in another. It was written to perform this task through formulae. So if there are changes in the source worksheet the result of the formula will be updated. If you will not use this feature then you can rewrite the code to pull the values ​​directly, this way the values will not be upgraded until you run the code again.

If you want try this way, change that same piece of code to this (which could be simplified later):

Please Login or Register  to view this content.