Loop copy and paste

[ScabbyDog]

I need a macro that copies range O1:O21 on sheet1 and then goes to sheet2 and checks to see if B2 is blank. If it is blank paste values into b2. If it is not then shift cells right and then paste values into b2. After it pastes the values, repeat the above 500 times. Possible?

[Norie]

Are you always pasting into B2?

[ScabbyDog]

Yes norie. Always b2, just the column will be shifted to the right so that b2 can be used for the new data.

[Mumps1]

Hi Scabby Dog. I have de-activated the lines that will make the macro loop 500 times because I'm not sure if this is what you wanted. Have a look at the code and let me know if it suits your purpose.

Please Login or Register  to view this content.

[ScabbyDog]

I need it to paste special, values as sheet1 contains formulas which it is currently pasting with your code.

[ScabbyDog]

Another option would be, instead of shifting the column right, just paste the values into the next column that is empty in the range b2:zz22

[AB33]

Try this one

Please Login or Register  to view this content.

[ScabbyDog]

Doesnt work. It inserts an entire column rather than shifting the cells to the right and it only runs once and not 500 times.

[ScabbyDog]

I have this which works but doesn't paste values.

Please Login or Register  to view this content.

[AB33]

Please Login or Register  to view this content.

[ScabbyDog]

Thanks Ab! We are close now. How would I add the loop to make it run 500 times now?

[AB33]

I am not sure which bit you want to repeat, but you can loop 500 times-500 rows

Please Login or Register  to view this content.