Capture header rows from all Excel worksheets in a directory to a separate master file

[marts2003]

Hi there,

I already have VBA code that will recursively trawl through a directory. I am currently using this to identify if any columns in any of the worksheets in any of the Excel workbooks within that directory contain email addresses and, if so, those columns are then deleted.


Here's the find and delete loop:

Dim wsCurrent1 As Worksheet
Dim B As Range
For Each wsCurrent1 In ActiveWorkbook.Worksheets
    Do
    Set B = wsCurrent1.Columns.Find(What:="*@*", LookIn:=xlValues, lookat:=xlPart)
    If B Is Nothing Then Exit Do
    B.EntireColumn.Delete
Loop
Next wsCurrent1

What I'd really like help with is to see if it's possible to modify this loop, so that instead of finding columns to delete, it copies the header row (of every worksheet in every workbook within the directory) and pastes them into a separate master file. I have seen code that more-or-less achieves this objective elsewhere - but not within the context of a RecursiveDir approach.


Any suggestions gratefully received (even if it's just to say it can't be done this way!).


Thank you,
Martin.