VBA Copy Columns Based on Header matching

[marcorui]

Hi, please help as I am very new beginner to VBA.

I need to copy and paste some data from Sheet 1 to Sheet 2.

The data are scattered and irregular through out the entire workbook. Now I want to find out how to copy the entire column to the new sheet based on the header.

Please help and thank you in advance!

 

[AlphaFrog]

Sub Copy_Columns()
    Dim vHeader As Variant, rngFound As Range, i As Long
    For Each vHeader In Array("District", "Address", "Contact Person")
        Set rngFound = Sheets(1).Cells.Find(vHeader, , xlValues, xlWhole, 1, 1, 0)
        i = i + 1
        If Not rngFound Is Nothing Then
            Range(rngFound, rngFound.End(xlDown)).Copy Destination:=Sheets(2).Cells(1, i)
        End If
    Next
    Sheets(2).Select
    Columns.AutoFit
End Sub

 

[marcorui]

AlphaFrog, Thank you very much, that works perfectly and it is solved now.

 

[rkb1510]

Sub Copy_Columns()
    Dim vHeader As Variant, rngFound As Range, i As Long
    For Each vHeader In Array("District", "Address", "Contact Person")
        Set rngFound = Sheets(1).Cells.Find(vHeader, , xlValues, xlWhole, 1, 1, 0)
        i = i + 1
        If Not rngFound Is Nothing Then
            Range(rngFound, rngFound.End(xlDown)).Copy Destination:=Sheets(2).Cells(1, i)
        End If
    Next
    Sheets(2).Select
    Columns.AutoFit
End Sub

Awesome alphafrog. I am looking for exact same operation but with little twist. I have to perform same task between two workbooks i.e. source.xlsx and target.xlsx

 

[Peter_SSs]

I am looking for exact same operation but with little twist. I have to perform same task between two workbooks i.e. source.xlsx and target.xlsx

Duplicate to: import specific columns from one workbook to another from matching header with vba

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.