Code to Reset Named Ranges when copy/paste to new file

[VickiMc]

Hello world,
My problem is that I am trying to replicate a (Payroll) Template that is
ultimately uploaded by that Dept as a DAT File for use with other Software.
I have copied all the Modules from the Template file so that for all intent
and (their) purposes the two files function the same, with the exception of
some of the named ranges. ('Some' being about 60).
There are three sections to the file (all on the one worksheet), one for
Hours Worked, below that is a section for Leave Taken, and below that one for
Allowances.
In my main Work File I copy & insert the three tables into a worksheet that
mimics the template (working from the bottom up), and then copy & paste the
whole lot into the replicate template, which is where it all starts to go
wrong.
Because I've copied & pasted into/onto the template I lose all the named
ranges.

So here's my question, is there 'Drop Dead' code for re-establishing the
named ranges once I've copied & Pasted onto the Replicate Template or should
I be creating them in my Work file prior to the pass?

NB: the three tables will vary in height (rows) every week.

 

[Joel]

I I'm copying into a template I would only have the named range the 1st
location of where the table is located and paste the table into the 1st
location of the named range. Yo can then get the end of the table using 1 of
the following methods

LastRow = Range("Start Table").end(xldown).row

or

LastRow = cells(Rows.Count),Range("Start Table").column).end(xlup).row

If you have any formulas that go at the end of the table of the template
then use Insert instead of paste to place the table.

 

[VickiMc]

Thank You Joel - Love Ya Work.
I'll now set about making all the necessary changes.
Here's hoping I don't need to continue the thread?
Cheers
VickiMc