VBA error 400 - macro to copy multiple cells

  Chillie 14:30 21 Jan 2011

Can an Excel expert kindly help me with this problem?
Using Excel 2003, I wish to copy a non-contiguous group of cells to a new part of the worksheet. Excel 2003 does not have this facilty, so googled and found this code, click here. However, I cannot get it to work. After inputting the 'paste to' cell reference, I get a VBA error 400. VBA Help does not offer any assistance.

I do hope someone can!

Many thanks


  VoG II 14:38 21 Jan 2011

It works for me.

Which line of code causes the error (click Debug).

  Chillie 15:04 21 Jan 2011

It works for me too in a fashion!

Copied the data into a new workbook and it was fine; must be something funny with my original workbook which has several worksheets.

But I must still be doing something wrong. If, for example, I have data in range A1 to B8, and select A2,B2; A4,B4; A6,B6; A8,B8; and apply the macro, the result is shown over 8 rows and not 4.

Any pointers?


  VoG II 15:12 21 Jan 2011

No you aren't doing anything wrong - that macro will not copy a non-contiguous range and paste as a contiguous block.

It is quite easy to get rid of the blank cells. Select the entire pasted range, press F5, click Special, tick Blanks and click OK. Then delete the selected cells.

  Chillie 15:44 21 Jan 2011

Once again VoG™, many thanks for your help.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires