vba - copy and paste selected columns to a new sheet, based on header name

[noidea23]

Hii, i have a raw data in sheet 1 and sheet 2 is supposed to be a condensed version (i.e. pulls selected columns from sheet 1).
I need vba to copy specific columns (based on column header), and paste the entire column into the first blank column in sheet 2.
I'm not very sure how to continue from the codes below. Help will be greatly appreciated!

Dim z
Dim x as Integer

z = Array("Language", "ID", "Date", "Names") ' column headers
Sheets("Sheet1").Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
x = Selection.Columns.Count ' count number of columns
LR = Range("A" & Rows.Count).End(xlUp).Row ' last row of column

For i = 1 to x
If Cells(1,i).Value = Name Then

 

[Alex Blakenburg]

I am sure someone will come up with a better way but this should work.
I have opted for using copy since one of the columns is a date and you will want the formatting to come across. We can modify if required.

Sub CopySpecificColumns()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    Dim srcLRow As Long, srcLCol As Long, destLCol
    Dim colArr As Variant
    Dim colName As Variant
    Dim ColNo As Long
    
    Set srcSht = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
    
    srcLRow = srcSht.Range("A" & Rows.Count).End(xlUp).Row          ' last row
    srcLCol = srcSht.Cells(1, Columns.Count).End(xlToLeft).Column   ' last column
    
    With srcSht
        Set srcRng = .Range(.Cells(1, "A"), .Cells(srcLRow, srcLCol))
    End With
    
    destLCol = destSht.Cells(1, Columns.Count).End(xlToLeft).Column   ' last column
    
    colArr = Array("Language", "ID", "Date", "Names") ' column headers
       
    For Each colName In colArr
        ColNo = Application.Match(colName, srcRng.Rows(1), 0)
        destLCol = destLCol + 1
        srcRng.Columns(ColNo).Copy Destination:=destSht.Cells(1, destLCol)
    Next colName
    
End Sub

 

[Fluff]

How about

Sub noidea()
   Dim Ary As Variant
   Dim Fnd As Range
   Dim i As Long
   
   Ary = Array("Language", "ID", "Date", "Names")
   
   With Sheets("Sheet1")
      For i = LBound(Ary) To UBound(Ary)
         Set Fnd = .Range("1:1").Find(Ary(i), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Intersect(.UsedRange, Fnd.EntireColumn).Copy Sheets("Sheet2").Cells(1, Columns.count).End(xlToLeft).Offset(, 1)
         End If
      Next i
   End With
End Sub

 

[noidea23]

How about

Sub noidea()
   Dim Ary As Variant
   Dim Fnd As Range
   Dim i As Long
  
   Ary = Array("Language", "ID", "Date", "Names")
  
   With Sheets("Sheet1")
      For i = LBound(Ary) To UBound(Ary)
         Set Fnd = .Range("1:1").Find(Ary(i), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Intersect(.UsedRange, Fnd.EntireColumn).Copy Sheets("Sheet2").Cells(1, Columns.count).End(xlToLeft).Offset(, 1)
         End If
      Next i
   End With
End Sub

Hello, thank you!! Both sets of code worked well

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[KaTa]

How about

Sub noidea()
   Dim Ary As Variant
   Dim Fnd As Range
   Dim i As Long
 
   Ary = Array("Language", "ID", "Date", "Names")
 
   With Sheets("Sheet1")
      For i = LBound(Ary) To UBound(Ary)
         Set Fnd = .Range("1:1").Find(Ary(i), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Intersect(.UsedRange, Fnd.EntireColumn).Copy Sheets("Sheet2").Cells(1, Columns.count).End(xlToLeft).Offset(, 1)
         End If
      Next i
   End With
End Sub

Hello,
I am new with VBA and I need your help.
What do I need to change in this code if the raw data in Sheet1 starts from A3 and destination is Sheet2 A1?

 

[Alex Blakenburg]

What do I need to change in this code if the raw data in Sheet1 starts from A3 and destination is Sheet2 A1?

Try this:
For Sheet1 - change this line from 1 to 3 (row 1 to 3 ie A3). Assuming row 3 is the heading row
Set Fnd = .Range("3:3").Find(Ary(i), , , xlWhole, , , False, , False)
For Sheet2 - you shouldn't need to change anything
this code is already assuming row 1 ie A1 > Sheets("Sheet2").Cells(1, Columns.count)