Simple copy paste vba

[Throughstream]

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

Sub Test4()
    Sheets("Rota week 4").Select
    Range("E33:AR135").Select
    Selection.Copy
    Sheets("Rota").Select
    Range("E33").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E33").Select
End Sub

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

Sub Test4()
   With Sheets("Rota week 4")
    Application.Union(.Range("E33:AR135"), .Range("E200:AR202"), .Range("E300:AR301")).Copy
   End with   
 Sheets("Rota").Select
    Range("E33").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E33").Select
End Sub

[alansidman]

Maybe this:

Option Explicit

Sub Test4()
    Dim s1 As Worksheet
    Set s1 = Sheets("Rota week 4")
    Dim range1 As Range, range2 As Range, range3 As Range
    Set range1 = s1.Range("E33:AR135")
    Set range2 = s1.Range("E200:AR202")
    Set range3 = s1.Range("E300:AR301")
    Union(range1, range2, range3).Copy
    Sheets("Rota").Range("E33").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    s1.Range("E33").Select
End Sub

[Throughstream]

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

Sub Rota_Button12_Click()
    Dim s1 As Worksheet
    Set s1 = Sheets("Rota week 4")
    Dim range1 As Range, range2 As Range, range3 As Range
    Set range1 = s1.Range("E33:G135")
    Set range2 = s1.Range("I33:K135")
    Set range3 = s1.Range("M33:O135")
    Union(range1, range2, range3).Copy
    Sheets("Rota").Range("E33").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    s1.Range("E33").Select
End Sub

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