Using Named References in Imported Sheets

[TheRedCardinal]

I have three named ranges in my main workbook that contains all my macros, RepMonth, RepYear and CoCode.

I have a series of other workbooks saved in a central location. As part of the macro, I open one of these workbooks, and copy over a specific sheet into my main workbook.

Contained in those individual workbooks are cells with basic formulae:

= RepMonth
= RepYear
= CoCode

Those names are not defined in the source sheets - so I get #Name error -which is fine (the sheets have no purpose until they are imported).

However when they move over, they still have the "#Name" error even though the specific named range is now present.

Do I need to apply a setting in the Workbooks.Open line to fix this?

Unlinked to this (or not?), Excel changed the formula to be =@RepMonth - the pop up tells me this shouldn't affect the outcome but maybe it has?

Any tips?

Thanks!

 

[Alex Blakenburg]

You aren't showing us your existing code and it sounds a bit of strange way of doing things ie using a range name in your source sheet that doesn't exist in that workbook but presumably you have a copy sheet line in there.
After the copy sheet try copying Formula2 on to itself as shown in the last line of the code below:

Sub CopyRangeNames()

    Dim srcWB As Workbook, destWB As Workbook
    Dim srcSht As Worksheet, destSht As Worksheet
   
    Set srcWB = Workbooks("20221031 Copy Sheet with range name TheRedCardinal.xlsx")
    Set srcSht = srcWB.ActiveSheet
   
    Set destWB = ThisWorkbook
   
    srcSht.Copy after:=destWB.Sheets(Sheets.Count)
    Set destSht = destWB.ActiveSheet
   
    ' Effectively reenter the formulas to refresh the use of the named range
    destSht.UsedRange.Formula2 = destSht.UsedRange.Formula2
   
End Sub

 

[TheRedCardinal]

Hi Alex,

Thanks for this. I suspected the answer would be along these lines as I found that selecting the cell, and pressing return, calculated the correct values.
I tried to use (using your code variables) destSht.Calculate to achieve this but to no avail.

Your method works perfectly.

Interested in you saying it's a strange way of doing it, so as I'm always looking to improve my programming, is there a better way to do it? Or is the concept just strange.

The process is as follows:

1. There is a single main workbook that my users use for their data preparation
2. There is a specific sheet that must be used but its contents vary depending on the value on the cell named "CoCode"
3. So once this CoCode is set, the macro finds the equivalent Checklist sheet for that code and imports it
4. But on the checklist, I populate it with lots of data that already exists in the main workbook, in named ranges
5. So I want the checklist to take the values of these named ranges and put them in the correct cells in the checklist

My code is relatively simple, enhanced by your line now:

Set WBk1 = ThisWorkbook
Set WS1 = WBk1.Sheets(1)

'Import Correct Checklist

Set CellA = Range("Checklists").Find(Range("CoCode"))

Set Wbk2 = Workbooks.Open(CellA.Offset(0, 1), UpdateLinks:=True)
Wbk2.Sheets("7. Checklist").Copy Before:=WBk1.Sheets("Data Sheet")
Wbk2.Close SaveChanges:=False

WBk1.Sheets("7. Checklist").UsedRange.Formula2 = WBk1.Sheets("7. Checklist").UsedRange.Formula2

Thanks for your help!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.
My comment was mainly relating to this:

Those names are not defined in the source sheets - so I get #Name error -which is fine (the sheets have no purpose until they are imported).

Which implies you have "errors" in your source sheets. But if it works for you then well and good.

 

[TheRedCardinal]

Thanks for letting me know. Glad I could help.
My comment was mainly relating to this:

Which implies you have "errors" in your source sheets. But if it works for you then well and good.

Thanks Alex.

I just assumed it was an "imported error" from the second workbook as the names the formulae were referring to were not defined.
I had (wrongly) assumed that once imported, and the name now referred to a range, it would automatically update, but apparently not.

All good!