Thanks. Silly me, I thought that once I replaced the old sheet with a new sheet with the same sheet name and range name Excel would be happy. I'll follow your suggestion and simply copy the new data to the old sheet, and redefine the range name.
Thanks again.
--- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...> wrote:
>
> You can have range names with the same names on different sheets, and Excel
> won't confuse them.
>
> Deleting your old sheet is going to orphan its range names and/or invalidate
> them from the VLookUps. Once that's been done, it isn't going to matter
> that you bring in a new sheet with the same range names ... the damage is
> already done.
>
> You probably want to simply copy the new data over the old, keeping the same
> sheet. However, you might then need to re-establish the range names, if the
> number of rows differs.
>
> You haven't said whether you are using VBA code here. If you are, then you
> simply need to get the VBA to redefine the range names. Or work without
> range names at all ... they really don't add much when using VBA.
>
> If your range names are purely intended to find the row extent of the data,
> then dynamic range names might be the way to go, if you can't simply
> dispense with them entirely and determine the data extent directly from the
> VBA.
>
> Regards, Dave S
>
> ----- Original Message -----
> From: "mickey11030" <mickey11030@...>
> To: <ExcelVBA@yahoogroups.com>
> Sent: Wednesday, November 02, 2011 8:42 PM
> Subject: [ExcelVBA] Replacing a Sheet with One of the Same Name
>
>
> >I have a workbook with 2 sheets. On Sheet1 I have a VLOOKUP formula that
> >references a range name FlightNumbers on the second sheet named Flight
> >Numbers. I have received an updated list of flight numbers. I thought the
> >easiest way to incorporate this new data would be to first define the range
> >name FlightNumbers on the new sheet to include all the data, then delete my
> >FlightNumbers sheet and finally to drag the new FlighNumbers sheet into my
> >workbook. But all my VLOOKUP formulas now show #REF.
> >
> > How should I incorporate the new data into my workbook?
> > Thanks,
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------
> > Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> > here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> > the ARCHIVES for EXCEL VBA.
> >
> > ----------------------------------
> > Visit our ExcelVBA group home page for more info and support files:
> > http://groups.yahoo.com/group/ExcelVBA
> >
> > ----------------------------------
> > More free tutorials and resources available at:
> > http://www.mousetrax.com
> >
> > ----------------------------------Yahoo! Groups Links
> >
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 10.0.1411 / Virus Database: 2092/3990 - Release Date: 11/01/11
> >
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar