Copy and Paste Transposing Non-Adjacent Ranges

[Guinaba]

Hi guys,

I have to copy this sheet where I have the following template (Pic1) into another sheet tranposing most of the ranges to have the final format (Pic2)

(Pic1)

(Pic2)

I was planning to use the following logic:

Sub Test()
    Dim rng As Range
    Dim r As Range
    Dim destination As Range

    Set rng = Union(Range("A1:B3"), Range("D1:E2"))
    Set destination = Range("H1")

    For Each r In rng.Areas
        r.Copy destination
        Set destination = destination.Offset(, 3)
    Next r
End Sub

Because my Calendar Week and WW Promo Week range expands to the right side every week I cannot use Range("H1:Z"&LastCol) I have to use Cells instead like:

Set CopyRng1 = ThisWorkbook.Sheets("Promo").Range(Cells(1, 8), Cells(2, LastCol)) but not sure what is missing because I keep getting the out of range error.