VBA excel column header

Question

I have ton of excel files that I am merging into 1 excel file and I need to put the column name or header on the first row. I have 3 columns: customerid, date, prediction. How do I put the column name on cell A1, B1, and C1 in VBA code? Thanks.

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("directory\path")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:C" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

You only want the column in A, B and C, even though you copy columns A to IV of each workbook? — Kyle
@Kyle Yes, just want column A1, B1, and C1. I just changed the code range to A2:C — sharp
@Comintern that made me laugh +1 - sharp - Macro recorder is your best friend! — Sorceri
One more for the pot: .cells(1, 1).resize(1, 3) = array("customerid", "date", "prediction") — user4039065

Gary's Student Gary's Student · Accepted Answer · 2016-10-25T19:56:51

Consider:

Sub whatever()
    Range("A1:C1").Value = Array("customerid", "date", "prediction")
End Sub

VBA excel column header

1 Answers