Copy Columns from multiple sheets to one master

[Utradeshow]

I have code that copies columns from one sheet to another, but I need it to copy from other sheets as well.

I want to also copy Columns A, D, F from SHEET 3 and put in SHEET 1 COLUMN H, I, J
also copy Columns G, H, X from SHEET 4 and put in SHEET 1 COLUMN K, L, M.

I can't figure out how to add this, I looked at other examples but they are even more complex.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim src As Worksheet
    Dim trg As Worksheet
    Dim LastRow As Long


    Set src = ThisWorkbook.Worksheets("SHEET 2")
    Set trg = ThisWorkbook.Worksheets("SHEET 1")




    src.Range("G:G").Copy Destination:=trg.Range("A1")
    
    src.Range("D:D").Copy Destination:=trg.Range("B1")
    
    src.Range("F:F").Copy Destination:=trg.Range("C1")
    
    src.Range("H:H").Copy Destination:=trg.Range("D1")
    
    src.Range("AA:AA").Copy Destination:=trg.Range("E1")
    
    src.Range("AR:AR").Copy Destination:=trg.Range("F1")
    
    src.Range("AS:AS").Copy Destination:=trg.Range("G1")

 

[Trebor76]

Hi Utradeshow,

Try this:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim trg As Worksheet, src As Worksheet
    Dim varSrc As Variant, varCols As Variant, varColsToFrom As Variant
    Dim i As Long
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
   
    Set trg = ThisWorkbook.Sheets("SHEET1")
    varSrc = Array("SHEET2", "SHEET3", "SHEET4") '<-Sheet names to have their data copied to 'trg'.
    varCols = Array("G-A;D-B;F-C;H-D;AA-E;AR-F;AS-G", "A-H;D-I;F-J", "G-K;H-L;X-M") '<-Columns to be copied from 'src' into columns within 'trg'
   
    For i = 0 To UBound(varSrc)
        Set src = ThisWorkbook.Sheets(CStr(varSrc(i)))
        For Each varColsToFrom In Split(varCols(i), ";")
            src.Range(CStr(Split(varColsToFrom, "-")(0) & ":" & Split(varColsToFrom, "-")(0))).Copy Destination:=trg.Range(CStr(Split(varColsToFrom, "-")(1)) & "1")
        Next varColsToFrom
    Next i
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert