VBA for Copying & pasting (with resizing) Named ranges from one workbook to another

[doctorhifi]

Any help on this one is appreciated.
I'm trying to come up with code to Copy & paste (with resizing) Named ranges from one workbook's worksheet to another workbook's worksheet

Details:

1. Source Workbook and Destination workbook will both be open (Destination workbook being ThisWorkbook with Source workbook being another excel workbook opened using: Set wk = GetObject(strBookName) )

-We are looking at sheets with the same name (Pricing Worksheet) in both the Destination and Source workbooks.



2. Start at Destination workbook at sheet named Pricing Sheet, moving through rows/columns A1 to AD190:

a. look at Named Range in Destination workbook, Pricing Sheet

b. find same Named Range in Source workbook, Pricing Sheet

-if Named Range not exists, record the name of the missing Named Range in a list to be displayed later (method TBD) and then move to next cell

-if Named Range exists, copy Named Range cell contents (which may be merged cells, and may contain formulas or plain text) and paste to Destination workbook, RESIZING for size of cell in the Destination. Resizing is important as the Source workbook is older and although should contain same Named Ranges the size of the ranges may have changed or been moved.



3. continue above until reaching AD190 and then display message box with list of Named Ranges not found. Alternately, perhaps the list of missing Named Ranges could be written to another worksheet in the Destination workbook for reference?

The code I currently use does a fine job of copying/pasting but does so blindly and not as I would like as described above

Partial code shown below:

'ShRead and shWrite are previously defined

Dim shRead As Worksheet, shWrite As Worksheet
Set shRead = wk.Worksheets("Pricing Worksheet") 'source
Set shWrite = ThisWorkbook.Worksheets("Pricing Worksheet") 'destination

Application.ScreenUpdating = False
' Copy from the old Pricing Worksheet to the current Pricing Worksheet
shRead.Range("A1:AD190").Copy shWrite.Range("A1:AD190")
wk.Close savechanges:=False ' Close the source workbook- the one being copied from

 

[doctorhifi]

'Bump

 

[rlv01]

Your explanation does not make sense to me. If you are searching for specific named ranges you need to know what the names are. If you are looping though the range A1 to AD190 , cell by cell, what is that going to tell you about named ranges? Particularly multi-cell ranges? Making a cell by cell copy decision as you loop though the range will signficnantly slow down your copy operation. You are better off doing a single copy operation as per your current code shRead.Range("A1:AD190").Copy shWrite.Range("A1:AD190").

 

[doctorhifi]

rlv01, thanks for the reply.
90% of the cells in A1 to AD190 already have named ranges. I thought there was a way to loop through A1 to AD190 and get the named range of each cell as it went. Perhaps multi-cell ranges (of which there are many) make this impossible.

The issue is, while the named ranges used in both the current workbook and the older workbook have been the same for quite awhile, some new named ranges have been added over time so the new workbook will contain named ranges that are not present in older workbooks. Additionally the size and location of the named ranges has changed over time so my current copy/paste method only works for older workbooks which have the identical layout.
I hope this explanation sheds some light on my initial post. Much appreciated.