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

I have two sheet (maybe more than) with 3 columns no specific order/arrangement and a Command Button in Sheet 2. How can I copy the content of all columns by searching the column name first and then copy the content

Please see attached image

Sheet 1 with data

Sheet 2 with command button to copy data from sheet 1 to sheet 2
Rowel VirgoVisual Studio .NETAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
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

Open in new window


Should you have more sheets the code would need to loop through the sheet
0
Roy CoxGroup Finance ManagerCommented:
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

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rowel VirgoVisual Studio .NETAuthor Commented:
hOW about by adding or copying next to the end of line? anyway, the codes sir is working
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Rowel
Which code you are referring to?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Open in new window

0
Rowel VirgoVisual Studio .NETAuthor Commented:
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.)
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In that case, replace line#8
Set dws = Sheets("Sheet2")

Open in new window


WITH
Set dws = ActiveSheet

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
My code copies to the next empty row of the master sheet.
CopyData.xlsm
0
LearnReporting Automation ExpertCommented:
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
0
Roy CoxGroup Finance ManagerCommented:
The last example attached is way less efficient than either of the other two. Why all the unnecessary selecting of sheets and ranges?
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@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
0
Rowel VirgoVisual Studio .NETAuthor Commented:
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

PIC
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Instead of posting image, please upload a sample workbook to work with.
0
Rowel VirgoVisual Studio .NETAuthor Commented:
Ok sir, here's the sample file. Thanks
0
Rowel VirgoVisual Studio .NETAuthor Commented:
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, please find the attached and click the button called "Input Raw Data" on Combine with VBA Sheet.
Book1-sample.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowel VirgoVisual Studio .NETAuthor Commented:
Sir Subodh Tiwari (Neeraj), Thank you for your help and support and also to all who response in my question.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Rowel!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.