EXCEL VBA Find Headers and Copy Column from Sheet 1 to Sheet 2

To achieve that, the following code will go underneath the button on Sheet2.
Place the following code on a Standard Module like Module1 and assign this code to the button on Sheet2. If it is a command button from Form Control, right click the command button and choose Assign Macros and select the macro CopyData and click OK. And if it is ActiveX command button, enable the design mode, double click the ActiveX command button and within the procedure inserted by default, call this macro like "Call CopyMode".

Sub CopyData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, dlc As Long, c As Long, col As Long
Dim colRng As Range, Rng As Range, Cell As Range
Application.ScreenUpdating = False

Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")

slr = sws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
dlc = dws.Cells(1, Columns.Count).End(xlToLeft).Column

For c = 1 To dlc
    Set colRng = sws.Rows(1).Find(what:=dws.Cells(1, c), lookat:=xlWhole)
    If Not colRng Is Nothing Then
        col = colRng.Column
        sws.Range(sws.Cells(2, col), sws.Cells(slr, col)).Copy dws.Cells(2, c)
    End If
Next c

Application.ScreenUpdating = True
End Sub

Select allOpen in new window

It would be easier to use proper Tables.

This will copy data only from Sheet1 to Sheet 2

Option Explicit

Sub CopyData()
    Dim rData As Range
    With Sheet2    ''/// sheet to copy to
        Set rData = Sheet1.Range("A1").CurrentRegion
        rData.Offset(1, 0).Resize(rData.Rows.Count - 1, _
                  rData.Columns.Count).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    End With
End Sub

Select allOpen in new window

Should you have more sheets the code would need to loop through the sheet

To copy from multiple sheets then use this. Place a shape or Forms button on the master sheet. Put the code into the master sheet module and assign to the button

Option Explicit

Sub CopyData()
    Dim oWS As Worksheet
    Dim rData As Range
    For Each oWS In ThisWorkbook.Worksheets
        If oWS.Name <> Me.Name Then
            With oWS   ''/// sheet to copy from
                Set rData = oWS.Range("A1").CurrentRegion
                rData.Offset(1, 0).Resize(rData.Rows.Count - 1, _
                                          rData.Columns.Count).Copy Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1)
            End With
        End If
    Next oWS
End Sub

Select allOpen in new window

hOW about by adding or copying next to the end of line? anyway, the codes sir is working

@Rowel
Which code you are referring to?

You may try this...

Sub CopyData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, dlr As Long, dlc As Long, c As Long, col As Long
Dim colRng As Range, Rng As Range, Cell As Range
Application.ScreenUpdating = False

Set sws = Sheets("Sheet1")
Set dws = Sheets("Sheet2")

slr = sws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
dlc = dws.Cells(1, Columns.Count).End(xlToLeft).Column
dlr = dws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

For c = 1 To dlc
    Set colRng = sws.Rows(1).Find(what:=dws.Cells(1, c), lookat:=xlWhole)
    If Not colRng Is Nothing Then
        col = colRng.Column
        sws.Range(sws.Cells(2, col), sws.Cells(slr, col)).Copy dws.Cells(dlr, c)
    End If
Next c

Application.ScreenUpdating = True
End Sub

Select allOpen in new window

I mean, this codes is copying data from sheet 1, what if I have another sheet, I preferred to add new button (Button 1 and 2) Button 1 will copy the data from sheet 1 with same condition, (per header or column name) and a button 2 to copy the data from another sheet so on.... next to the last line that Btn1 copied (if possible with no blank rows.)

In that case, replace line#8

Set dws = Sheets("Sheet2")

Select allOpen in new window

WITH

Set dws = ActiveSheet

Select allOpen in new window

My code copies to the next empty row of the master sheet.
CopyData.xlsm

Hi Rowel Virgo

Please find attached Macro file for your reference, just small change instead of Sheet2 i used Sheet 1 to run the Macro,

Please check and let me know for any change require

Thanks and Regards,
Learn Excel in Tamil
Mail us: learnexcelintamil@gmail.com

Subscribe on YouTube : https://www.youtube.com/channel/UC5vGAvycUz41AzyUP7vi61Q 
Like our page in Facebook : https://www.facebook.com/Learnexcelintamil/
Copypaste.xlsm

The last example attached is way less efficient than either of the other two. Why all the unnecessary selecting of sheets and ranges?

@Roy

Maybe we need to join his excel training program and then only we can understand the logic of selecting the sheets and ranges.  LOL

Thanks
Please see this picture, I have three sheets to combine in one sheet but to search first the header and copy the data on that column if match to Combine Sheet
The first code works but I need to combine Three sheets

Instead of posting image, please upload a sample workbook to work with.

Ok sir, here's the sample file. Thanks

Book1-sample.xlsx

Sir Subodh Tiwari (Neeraj), Thank you for your help and support and also to all who response in my question.

You're welcome Rowel!