Select Excel Table values

Hello Kubas,

Welcome to the Forum!

This macro will take the information fro columns "A:B" of "Sheet1" and create headers in row 1 of "Sheet2". Change the sheet names in macro if you need to

Sub CreateHeaders()

  Dim Cell As Range
  Dim DstWks As Worksheet
  Dim Header() As Variant
  Dim I As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim SrcWks As Worksheet
  
    Set SrcWks = Worksheets("Sheet1")
    Set DstWks = Worksheets("Sheet2")
    
      Set Rng = SrcWks.Range("A1")
      Set RngEnd = SrcWks.Cells(Rows.Count, Rng.Column).End(xlUp)
      If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = SrcWks.Range(Rng, RngEnd)
      
      For Each Cell In Rng
        ReDim Preserve Header(I)
          Header(I) = Cell & " " & Cell.Offset(0, 1)
        I = I + 1
      Next Cell
      
    DstWks.Range("A1").Resize(1, I).Value = Header
      
End Sub

.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Hello Kuba,

If you declare a variable as a Range then you can assign an entire row to the variable. You will still need to use the cells property to return or set any individual values.