Hi,

perhaps you should investigate into a different approach.

I should do that the following way if i were you :
1) Record a first VB Script inside your excel worksheet that stores all the command required to set your header line the way you want. Normally , Macro -> Record new macro -> use F2 upon every header field without modifying and after header finished -> Macro -> Stop recording.
2) Check your macro code and rename it as whished. I assume You call this Macro HEADER_FORMAT
3) For the Total , simply use the same way to record a new Total Formatting Macro, but do not forget to start macro recording before positioning your cursor inside the first field.
4) Check your Macro code and rename it as wished. I Assume you call this MAcro TOTAL_FORMAT
5) Clear completely this sheet.
6) Import Your data inside this empty excel sheet
7) Run MAcro HEADER_FORMAT to get YOUR header
8) Now this is time to modify your TOTAL_FORMArt macro to fit actual number of records, any value this number is and will be.

Assuming you need to Summarize column B lines 2 to 7 at cell(B8), your macro code should be like

    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"

Place the following lines in the top of your TOTAL_FORMAT macro

    Dim lrow        As Long
    Dim xlrow       As String
    lrow = ActiveSheet.UsedRange.Rows.Count
    xlrow = lrow

This will store the number of used lines, header included, inside variables lrow and xlrow.

Now modify the lines

    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"

to

     Range_id = ("B" & xlrow)
    Range(Range_id).Select
    ActiveCell.Formula = "=SUM(R[" & -lrow + 1 & ">C:R[-1]C)"

This will put the cursor in column B above last data line and create a total from Row 2 to lrow stored and B(lrow+1).

That’s it.
Just do the same with other columns.

9)Now save your file (allways before testing) and run macro TOTAL_FORMAT.

This should do the tric.

Now every time you want to update your file.
1) Clear the Sheet
2) Run HEADER_FORMAT macro.
3) Import your data
4) Run TOTAL_FORMAT.

The final solution could go really further with automatization of all the process with one single button, doing clear, import, formatting and many more if required. (Pivot table…..)

If you need a more sophisticated solution example, just send a private mail to me (my ID is TSCHMITZ) with your email and name, and i’ll contact you as soon as i can

I hope i may help you.

Thierry Schmitz.