Simple copy paste vba

[Throughstream]

Hi this is probably really simple but I have vba like this:

Please Login or Register  to view this content.

Which copies e33:ar135 from one sheet to another. However say I want to copy more than one range so E33:AR135 and E200:AR202 AND E300:AR301 for example how do copy and pastespecial each of the ranges in one go rather than writing out lots of copy and paste lines. Also if it could do it without physically going to the sheet it's copying that would be great.

Many thanks

[ImranBhatti]

Maybe like this

Please Login or Register  to view this content.

[alansidman]

Maybe this:

Please Login or Register  to view this content.

[Throughstream]

Hi many thanks, both of those formula's work. The issue I have now is that if I change the range to say:

Please Login or Register  to view this content.

When it pastes the I33:K135 it doesn't paste them in the right place, both sheets are identical so I'd like the range to be pasted into their respective areas i.e Rota week 4 I33:K135 would paste into Rota I33:K135 and so on. Sorry i should of explained myself better, that's my fault. The idea behind this is I have protected cells on sheet "Rota" which is stopping pasting certain columns in.

Thanks