PDA

View Full Version : Solved: copy reference without link from copyed file



white_flag
01-19-2012, 05:32 AM
Hello I have an sheet with data (as an master sheet) from this one I copy to another file some data (as slave)


ThisWorkbook.Sheets("data").Copy After:=wb.Sheets("Sheet1")

in the master sheet I have some name data will defined some ranges as reference. with the above code the data will be copied with reference to master. Can be copied as reference to herself to to master (because the data will be inside of the slave).

='[MasterWorkbook.xls]Data'!$C$644:$M$667 to be
=Data'!$C$644:$M$667

mancubus
01-20-2012, 01:54 AM
hi.

this is from my friend macro recorder... :)
(i remember reading a quote like "macro recorder is your friend" somewhere..)


add these lines after line(s) that copies worksheet...


ActiveWorkbook.ChangeLink Name:= _
"MasterWorkbook.xls", NewName:="New Workbook.xls", _
Type:=xlExcelLinks


ps: if the formulas in the original workbook have links to other worksheets, those worksheets must be available in the New Workbook... if not, copy them too)

mancubus
01-20-2012, 03:26 AM
cross-posted here:
http://www.mrexcel.com/forum/showthread.php?p=3007564&posted=1#post3007564

pls refer to crossposting rules...

white_flag
01-20-2012, 03:29 AM
this is a cross-posting see:

http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=3007562

white_flag
01-20-2012, 03:39 AM
ActiveWorkbook.ChangeLink Name:=ThisWorkbook.Name, NewName:=wb.Name, Type:=xlExcelLinks

but the links are still there

white_flag
01-20-2012, 03:45 AM
sorry, your code line was good. now it is ok :) thx mate

mancubus
01-20-2012, 06:18 AM
you're wellcome..