Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
'External links' in formulas when the user paste cells to other workbook?
Hi,
The workbook have in formulas links between sheets and it will become a 'external link' to this workbook if the user copy&paste cells to other workbooks. I have a huge amount of formulas, cells and sheets and feel I can't make 'VBA coded links' to all of it (like 'Let Sheet1.Range("A1").value = Sheet2.Range("A1").value') I want the pasted cells have the same formulas as in the first workbook and NOT change the formula to link to the WB I copied the cells I copied cells from. This is even more frustrating when I also use formulas and links as 'named ranges'. I wonder if I could exchange the formulas with a VBA function, like instead of writing: =Sheet2!A1*2 , I can address a function named "SheetAdd": =SheetAdd("sht2") & A1 and in VBA have: Function SheetAdd (str as string) If str = "sht2" then SheetAdd = "Sheet2!" End if End function This doesn't work of cource, since '&A1' refere to A1 in the activesheet, but ... Suggestions? /Regards Tskogstrom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
'External links' in formulas when the user paste cells to otherworkbook?
I do this when I do it manually:
Select all the cells edit|Replace what: = (equal sign) with: $$$$$ replace all Then copy that sheet to its new home. Since all the formulas are just plain old text, there are no links back to the original workbook. Then I do two more edit|Replaces to change the $$$$$ back to =. (Original and new worksheets.) If I need code, I'd record a macro when I did it manually. tskogstrom wrote: Hi, The workbook have in formulas links between sheets and it will become a 'external link' to this workbook if the user copy&paste cells to other workbooks. I have a huge amount of formulas, cells and sheets and feel I can't make 'VBA coded links' to all of it (like 'Let Sheet1.Range("A1").value = Sheet2.Range("A1").value') I want the pasted cells have the same formulas as in the first workbook and NOT change the formula to link to the WB I copied the cells I copied cells from. This is even more frustrating when I also use formulas and links as 'named ranges'. I wonder if I could exchange the formulas with a VBA function, like instead of writing: =Sheet2!A1*2 , I can address a function named "SheetAdd": =SheetAdd("sht2") & A1 and in VBA have: Function SheetAdd (str as string) If str = "sht2" then SheetAdd = "Sheet2!" End if End function This doesn't work of cource, since '&A1' refere to A1 in the activesheet, but ... Suggestions? /Regards Tskogstrom -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
'External links' in formulas when the user paste cells to other workbook?
Hi,
sorry but it doesn't solve my problem. I reduce the users possibilities to change into the workbook by workbook protection and I also use formulas in 'named cells' formula field. These formulas could turn into external links if the user copy paste certain cells. The users workbook is protected and therefore the user can't change back the external links (and most users will not be skilled enough to find the links). Regards tskogstrom Dave Peterson skrev: I do this when I do it manually: Select all the cells edit|Replace what: = (equal sign) with: $$$$$ replace all Then copy that sheet to its new home. Since all the formulas are just plain old text, there are no links back to the original workbook. Then I do two more edit|Replaces to change the $$$$$ back to =. (Original and new worksheets.) If I need code, I'd record a macro when I did it manually. tskogstrom wrote: Hi, The workbook have in formulas links between sheets and it will become a 'external link' to this workbook if the user copy&paste cells to other workbooks. I have a huge amount of formulas, cells and sheets and feel I can't make 'VBA coded links' to all of it (like 'Let Sheet1.Range("A1").value = Sheet2.Range("A1").value') I want the pasted cells have the same formulas as in the first workbook and NOT change the formula to link to the WB I copied the cells I copied cells from. This is even more frustrating when I also use formulas and links as 'named ranges'. I wonder if I could exchange the formulas with a VBA function, like instead of writing: =Sheet2!A1*2 , I can address a function named "SheetAdd": =SheetAdd("sht2") & A1 and in VBA have: Function SheetAdd (str as string) If str = "sht2" then SheetAdd = "Sheet2!" End if End function This doesn't work of cource, since '&A1' refere to A1 in the activesheet, but ... Suggestions? /Regards Tskogstrom -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
'External links' in formulas when the user paste cells to otherworkbook?
I think the only two options are for your code to do it (edit|links|change
source) or to have the user do it manually. I can't think of any other ways to do it. tskogstrom wrote: Hi, sorry but it doesn't solve my problem. I reduce the users possibilities to change into the workbook by workbook protection and I also use formulas in 'named cells' formula field. These formulas could turn into external links if the user copy paste certain cells. The users workbook is protected and therefore the user can't change back the external links (and most users will not be skilled enough to find the links). Regards tskogstrom Dave Peterson skrev: I do this when I do it manually: Select all the cells edit|Replace what: = (equal sign) with: $$$$$ replace all Then copy that sheet to its new home. Since all the formulas are just plain old text, there are no links back to the original workbook. Then I do two more edit|Replaces to change the $$$$$ back to =. (Original and new worksheets.) If I need code, I'd record a macro when I did it manually. tskogstrom wrote: Hi, The workbook have in formulas links between sheets and it will become a 'external link' to this workbook if the user copy&paste cells to other workbooks. I have a huge amount of formulas, cells and sheets and feel I can't make 'VBA coded links' to all of it (like 'Let Sheet1.Range("A1").value = Sheet2.Range("A1").value') I want the pasted cells have the same formulas as in the first workbook and NOT change the formula to link to the WB I copied the cells I copied cells from. This is even more frustrating when I also use formulas and links as 'named ranges'. I wonder if I could exchange the formulas with a VBA function, like instead of writing: =Sheet2!A1*2 , I can address a function named "SheetAdd": =SheetAdd("sht2") & A1 and in VBA have: Function SheetAdd (str as string) If str = "sht2" then SheetAdd = "Sheet2!" End if End function This doesn't work of cource, since '&A1' refere to A1 in the activesheet, but ... Suggestions? /Regards Tskogstrom -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links to External Workbook | Excel Worksheet Functions | |||
how can i delete external links from my workbook | Excel Discussion (Misc queries) | |||
How to past formulas without updating external links | Excel Programming | |||
Links in formulas change when another user runs a workbook - 2003 | Excel Programming | |||
Links in formulas change when another user runs a workbook | Excel Discussion (Misc queries) |