• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 104
  • Last Modified:

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
0
Rowel Virgo
Asked:
Rowel Virgo
  • 8
  • 6
  • 4
  • +1
1 Solution
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
LET (Learn Excel in Tamil)Reporting 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now