Hi Kim,

Names can be local (sheet level) or global (workbook level). If you use
names, they should almost always be local so they travel with the sheet when
copying to other workbooks, ..without conflicts. For example, you can use the
same name on as many sheets as you want within any single workbook without
conflict because the name is "proprietary" to the sheet it's defined on.

Here's how that works:

For each sheet that contains a similar range of data, define a local name
for the range like this:

'Sheet Name'!RangeName

If the base range is A2:G20, select it before defining the name.

Note the sheetname is wrapped in an apostrophe. This handles spaces and
other characters that are "legal" to use in names.

Note also, the exclamation character follows the sheetname, and tells Excel
the rangename is coming next.

The rangename here could also be written as Range_Name, or any other format
that contains no spaces. (not the same as allowed spaces in the sheetname)

Since the macro copies the same range (no matter what size it is) from each
sheet, a qualified reference to the sheet is all that's required. For
example, you mentioned in your post: "that the template has to be formatted
a certain way". This is typical of all accounting software apps that import
data from Excel spreadsheets. The key here is this, -Is your template a real
template (as in ".xlt") or just a sheet you copy when you need to perform
this task? Could it be set up as a template so every copy has the same
format, named ranges, and structure? -Yes it can! Then your macro will work
with any 'copy' of this sheet you run it on.

If it needs to be the active sheet then just select that sheet before
running the macro.

If you're looping through a number of sheets then just qualify a reference
to each sheet before running the macro on it. (loop structures do this by
default)

In the case of people inserting/deleting rows between A2 and G20, the named
range will adjust itself to include them.

In the case of adding rows after the last row, a named dynamic range would
be better. Of course this creates a problem with the SUM() formula for your
totals, but that can be prevented by using a named relative range as follows:

Assumes formula in C21 is =SUM(C2:C20)

Select any cell that has a total in it. Say C21
Define a local name like 'SheetName'!LastCell
In the RefersTo box enter =C20
Click "Add", "OK".

This creates a relative reference to the cell above the one containing the
formula the name is used in. To use it, change the formula in C21 to
=SUM(C2:LastCell). Now, you can insert rows above the totals row and you
formula adjusts to include them.

The links I posted will help you with creating/using dynamic ranges. If you
would like me to look at your template file or 'structure' it for you, post
back and I'll give you my emailing info. Try to keep the file as close to
"actual" scenario as possible. If the file contains macros, you'll need to
zip it to get through the firewall.

HTH
Regards,
Garry