Combine columns in excel [Solved/Closed]

 Carman -
i have got two sheets of data and i have arranged them into ideal orders. Now i want to place data on sheet 2 behind data on sheet 1.( in the same region/column)I need a VBA code to perform the job as further update is needed.

please take a look at the reference file:

Sheet 3 is the output form that i want but i would like it to be in sheet 1 when the job is done.


2 replies

Dear Carman,

Please get through the following Kioskea FAQ article in order to get the task completed.

Thank you.
Registration date
Thursday January 28, 2010
Last seen
August 2, 2020
Try this
This is based on assumption that columns in both sheets are in same order and at same location

Try this

Sub CopyData()
Dim lSrcMaxRows As Long
Dim lTgtLastRow
Dim Cell As Range

Dim sTgtSht As String
Dim sSrcSht As String

    sSrcSht = "Sheet2"
    sTgtSht = "Sheet1"
    Sheets(sSrcSht).AutoFilterMode = False
    Set Cell = Sheets(sSrcSht).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Cell Is Nothing Then Exit Sub
    lSrcMaxRows = Cell.Row
    Sheets(sTgtSht).AutoFilterMode = False
    Set Cell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Cell Is Nothing Then
        lTgtLastRow = 0
        lTgtLastRow = Cell.Row
    End If
    lTgtLastRow = lTgtLastRow + 1
    Application.CutCopyMode = False
    If (lTgtLastRow = 1) Then
        Rows("1:" & lSrcMaxRows).Copy
        Rows("2:" & lSrcMaxRows).Copy
    End If
    Cells(lTgtLastRow, "A").PasteSpecial
    Application.CutCopyMode = False
    Set Cell = Nothing

End Sub
Your code works perfectly well!!
Thank you very very much!!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!