VBA PROGRAMME REQUIRED FOR COPY PASTE SPECIAL

[Abhi07]

Hi


VBA PROGRAMME REQUIRED TO PERFORM BELOW TASKS;

1. COPY DATA FROM THE COLUMNS (CG) AND PASTE INTO MERGED COLUMN (CP,CQ,CR)
2. COPY DATA FROM THE COLUMNS (CH) AND PASTE INTO MERGED COLUMN (CS, CT, CU, CV)
3. COPY DATA FROM THE COLUMNS (CI, CJ, CK, CL), CONCATENATE (JOIN) IN COLUMN WITH LINE BREAK (ENTER) BETWEEN DATA THEN PASTE INTO MERGED COLUMN (CW, CX, CY, CZ, DA, DB, DC, DD)

NOTE THAT THESE COLUMNS ARE MERGED, THIS IS TO ENABLE EASY COPY/PASTE INTO ANOTHER EXCEL. DATA SHOWN ABOVE IS IN SINGLE ROW, HOWEVER IN ACTUAL IT MAY BE MORE THAN 9000 ROWS.

 

[mumps]

This macro assumes you have headers in row 1 and your data starts in row 2.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("CG2", Range("CG" & Rows.Count).End(xlUp)).Resize(, 6).Value
    For i = LBound(v) To UBound(v)
        Range("CP" & i + 1) = v(i, 1)
        Range("CS" & i + 1) = v(i, 2)
        Range("CW" & i + 1) = Join(Application.Transpose(Application.Transpose(Array(v(i, 3), v(i, 4), v(i, 5), v(i, 6)))), Chr(10))
        Rows(i + 1).RowHeight = Rows(i + 1).RowHeight + 45
    Next i
    Application.ScreenUpdating = True
End Sub