import Named Ranges?

Hi,

I am importing an external Sheet into my Excel workbook but it only imports the data not the named ranges. I need the named ranges to be imported and overwrite any existing range of same name.

Example:

In existing workbook Sheet I have a named range, ABC which is A1:E10 - in the Sheet I am importing the ABC range is A1:E12. So when I have done import I want named range ABC in my workbook to become A1:E12 (as per external Sheet).

This is the code that import data (but not named ranges)

Please Login or Register  to view this content.

What do I need to code? Help!

cheers

If you copy in the whole sheet instead of just the cells, it will keep the named ranges.

Try this

Please Login or Register  to view this content.

The Named Ranges are only visible within the new worksheet - they are not visible from any other sheets unlike the pre-existing Named Ranges that were global. Can this be resolved?

Originally I wanted it to load into existing worksheet - where I have an activeX button - but I can work around that by coding it to delete existing sheet and place invoke button in different sheet.

The Named Ranges are visible from all sheets in the workbook when I've tried it. Have you checked that you don't already have that name with an invalid reference from a previous attempt?

If you look in Insert - Name - Define - .... is there a copy of the name from the deleted sheet? It will have a #ref! in the "refers to" box. If there is, you'll need to delete it.

When I delete the Named Ranges (vba) - then import from external Sheet they are visible under Define but they point at the external Sheet.

When I do not Delete the Named Ranges - they exist twice, first locally, then the global value is #REF (and not visible in other Sheets).

Either way I have a problem.

This is the code with the deletes in place.

Please Login or Register  to view this content.

I have deleted Named Ranges by VBA and also through the Name Manager GUI - in both cases the same Named Ranges from the External Sheet (after copy) refers to the External sheet itself, i.e. Value = #REF!

I also added a new Named Range to the External Sheet and that was copied in successfully, i.e. referring to itself locally.. and visible globally to other sheets within workbook. So it sounds like to me that there is something not right with the Delete process (whether by VBA or front end of Name Manager) as if it did not previously exist not a problem.

The code

Please Login or Register  to view this content.

will only delete workbook ranges ie those that are visible on all sheets. Is it possible that you also have a range with the same name on another sheet? You could delete it with something like

Please Login or Register  to view this content.

I haven't been able to replicate your problem so if this doesn't work it would really be best if you could upload sample files, as shg suggested.

Have written solution for my OP, i.e. correctly updating Named Range

...
ws.Range("A1").PasteSpecial
Resize("ABC")

Please Login or Register  to view this content.