: If the script has to locate the data in each sheet, does the
: range of cells ever contain empty cells?
: No. Also there is only one sheet  in both file  which contains
: data

Are you implying that there may be extra blank worksheets
in one or both workbooks.


: Will it ever contain empty cells in the future?
: No

Fine. We can use the UsedRange property to define all the data
in each sheet.


: How do we find the header  in the second file? Is it always in
: a certain row? At the top of the data? Is it in just one row or
: many? Is it a named range?
: At the top of the data, first row in both file and just one row

If the sheet is the active sheet:

Dim rData As Range, _
rHeader As Range, _
rBody As Range

Set rData = ActiveSheet.UsedRange.Cells
Set rHeader = rData.Rows(1)
Set rBody = rData.Rows("2:" & rData.Rows.Count)



Great. We're almost there. What are the names of the files  used
to supply the data? Are they always the same names or do they change
each time? What is the name of the new workbook file?