Copy and Paste Transposing Non-Adjacent Ranges

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
92
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)

1608700916234.png


(Pic2)
1608700829104.png



I was planning to use the following logic:

VBA Code:
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,127,551
Messages
5,625,457
Members
416,107
Latest member
AVaes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top