Thank you for your response. The idea is for the financial
transactions for the month to be imported into one sheet (this is
done via the SQL with defined ranges being used as criteria) and then
another sheet would to the calculations (financial report template).
On completion, the two worksheets (data & report) would be exported
to a new workbook as values only, save, close and then continue
through.

This is typical period reporting/analysis activity which I have some
deal of experience with and so is what I suspected you were doing.

What I found was that the defined ranges that I manually created,
would get deleted when any existing transaction data was deleted.

Correct! This is because most accounting systems 'Clear' the sheet
before updating with new data. (Assumes the data is written directly to
a specific sheet in a specified workbook) *OR* the import process will
'Clear' the sheet for new data. I don't think 'Refresh' has the same
effect on defined name ranges but can't say for sure!

Here's how I handle this for my clients! I design their
'report/analysis' sheet[s] as desired for layout/formatting. I use
lookup formulas to pull data from the 'Data' sheet as required for the
fields on these sheets. This is based on the default layout of the
imported data so field info locations are reliably consistent. Now my
client can reuse this workbook every period by refreshing the 'Data'
sheet appropriately. The report/analysis sheets update automatically
when the new data is available. Same as you do, the results get copied
to a fiscal consolidation workbook as 'values'.

The 'Data' sheet is a plain worksheet without formatting, and has no
defined names.

Any defined names on the report/analysis sheet[s] have local scope so
there's no 'name conflicts' when period sheets are copied to the
consolidation workbook.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion