Copying Sheet with Data Validation List

[bmoneymillionaire]

Hi All,

I looked around quite a bit, but could not find an answer to my question. If this is addressed somewhere else, I apologize.

Basically, I have a macro that copies sheets to another workbook, among other things. The problem is that when the cells with drop-down menus are copied, the data validation source switches from an internal reference (e.g., =Sheet2!$A$1:$A$8) and becomes an external reference (e.g., =[Workbook.xlsx]Sheet2!#REF!).

Is there a way to keep the list source reference internal? (For certain reasons, I am not interested in work-arounds with named selections, indirect(), etc.)

Any help would be greatly appreciated. Thank you!

 

[rayk8301]

looks like your best bet would be to use power query which there are some videos on that on youtube under excelsfun.

 

[bmoneymillionaire]

Thanks rayk - Is there a certain video on this channel I should take a look at? I'm not familiar with power query and there are a lot of videos on that channel. Maybe there is a simpler solution that does not require power query?

 

[rayk8301]

you can simply search for power query within that channel. I don't know of an easier way without using power query.

 

[bmoneymillionaire]

Thank you - Much appreciated! Still interested in other methods if anyone knows of any