copy range into new sheet (concatenated columns, every second column)

[snptin]

Dear VBA experts,

I am planning to create Avery labels directly in Excel (by formatting the page and cell sizes) and in desperate need of solution to transfer the data into the label sheet appropriately.
In the attached example, there is input label data on the Sheet1 = 5 columns with data per label. Each column = one line on the label. Every row = new label. In this example there are 2 labels repeated 8 and 13 times, respectively.
I would like to concatenate all 5 columns per row and paste them into the new sheet starting in A1, every 2nd column until column 7, then start new row and repeat until all labels are on the sheet.
An example how the labels should look like in on the Sheet2.
Page Formatting part I can write on my own

Thank you in advance for all the suggestions!

[Marc L]

According to your attachment a VBA demonstration for starters :

Sub Demo1() 
    Dim C%, R&, Rw As Range 
        C = 1 
        R = 1 
        Sheet2.UsedRange.ClearContents 
    For Each Rw In Sheet1.[D2].CurrentRegion.Rows 
        Sheet2.Cells(R, C) = Join(Application.Index(Rw.Value, 1, 0), vbLf) 
        If C = 7 Then C = 1: R = R + 1 Else C = C + 2 
    Next 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[HansDouwe]

With an formula, please try

Please Login or Register  to view this content.

[snptin]

Thank you, Mark and thank you, Hans! - exactly what I need (although only as VBA option at the moment).