Multiple Column and string extract

[Emanuele]

Hi guys, I would like to know if there is a way to add more than 1 adjacent column for the presented code that I found on Internet and that Fluff helped me to modify in this Post
Furthermore it's possible to apply a string extract (for example, extract the first 4 and the last 4 characters and put them in different column)?
Thank you

    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Statistics"
    ' Find the last column with data on the summary
    ' worksheet.
    Last = LastCol(DestSh)
    ' Loop through all worksheets and copy the data to the
     'summary worksheet.
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> DestSh.Name Then
            ' Fill in the columns that you want to copy.
            Set Copy1 = sh.Range("C8:AZ8")
            'Set Copy2 = sh.Range("C10:AZ10") 'second possible column'
           'Set Copy3 = sh.Range("C11:AZ11") 'extract the first 4 characters and put in a column then the last 4 characters and put in adjacent column

            ' This statement copies and transpose the valuse
            With Copy1
            .Value
                DestSh.Cells(Rows.Count, Last + 1).End(xlUp).Offset(1).Resize(.Columns.Count).Value = Application.Transpose(.Value)
            End With
           ' With Copy2
           '     DestSh.Cells(Rows.Count, Last + 1).End(xlUp).Offset(1).Resize(.Columns.Count).Value = Application.Transpose(.Value)
           'End With
           ' With Copy3
           '     DestSh.Cells(Rows.Count, Last + 1).End(xlUp).Offset(1).Resize(.Columns.Count).Value = Application.Transpose(.Value)
           'End With
        End If
    Next

 

[LazyBug]

Hello!
Can you show some sample input data and list (step by step) tasks for a macro using specific ranges of it?

 

[Fluff]

Duplicate Multiple column and String extract VBA

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).