Run-time error 1004: Reference is not valid with Selection.Consolidate and Named Ranges

[marc005]

Apparently named ranges does not allow you to change the data using code instead you
need to type in the new observations. Consider this:

1) Download the two files; Merg.xlsm and Australia - Close.xlsm to your desktop

https://dl.dropboxusercontent.com/s...gZfZaDuLS8155L0_q_kJZxHcX3GEKKkk02tToeyg&dl=1
https://dl.dropboxusercontent.com/s...rN0QcjK1IM51pIAeUg1bd2ZGEDxvtmcRSneGLYnA&dl=1

2) Hold down shift when you open the file Australia - close.xlsm. Then go to the VBA editor and in the procedure Test1 in ThisWorkbook
change the url Application.Workbooks.Open ("C:\Users\marc\Desktop\Merg.xlsm") to match your desktop.

3) Click on Australia - close.xlsm

4) Click on Merg.xlsm and then go to the VBA editor and in the procedure Merge in Module1
change the url y = "'C:\Users\marc\Desktop\Merg.xlsm'" to match your desktop.

5) Click on Merg.xlsm and run the procedure Merge in Module1
You will now get an error message: Run-time error 1004: Reference is not valid
The named reference will become =OFFSET(#REF!#REF!,0,0,COUNTA(#REF!#REF!),COUNTA(#REF!#REF!))
Instead of =OFFSET(Australia!$A$1,0,0,COUNTA(Australia!$A:$A),COUNTA(Australia!$1:$1))

6) In the procedure Merge remove z13 (z13 = y & "!Australia" where y = "'C:\Users\marc\Desktop\Merg.xlsm'")
in Selection.Consolidate Sources(z1, z2.......z13 , z14 etc )

7) Now everything works as it should!


This is very very strange! The formula for the named ranges appears to become corrupt when you update the data with code.
hummmmm...

 

[marc005]

I wonder how to solve that?

 

[marc005]

maybe I can use some other method instead of name ranges....

 

[p45cal]

OK, I know it's 6 days since you posted, but the files you linked to seem no longer to be there..

 

[marc005]

sorry for that. I went another direction and scipt the named ranges. Now I just download everything
in three columns like a million rows and then I run a pivot table on it. Much easier. I should have done that from the start. maybe I should delete this post.