If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copying formulas between sheets and keeping original ref
Hi all
I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#2
|
|||
|
|||
Copying formulas between sheets and keeping original ref
Try to use absolute references!
Regards, Stefi „Walter” ezt *rta: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#3
|
|||
|
|||
Copying formulas between sheets and keeping original ref
Give an example of the formulae which are causing problems.
Pete On Aug 5, 11:01*am, Walter wrote: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want.. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#4
|
|||
|
|||
Copying formulas between sheets and keeping original ref
I cant do that.
"Stefi" wrote: Try to use absolute references! Regards, Stefi „Walter” ezt *rta: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#5
|
|||
|
|||
Copying formulas between sheets and keeping original ref
Then try a solution of this kind:
Sub test() Cells(ActiveCell.Row, ActiveCell.Column + 1).Formula = _ "=" & Mid(ActiveCell.Formula, 2) End Sub This sub copies the formula in the active cell into the cell same row, next column, without changing the original references. Adjust it to your needs! Regards, Stefi „Walter” ezt *rta: I cant do that. "Stefi" wrote: Try to use absolute references! Regards, Stefi „Walter” ezt *rta: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#6
|
|||
|
|||
Copying formulas between sheets and keeping original ref
Hi Pete.
Formulas are different, they might be easy like =A2 or more complicated like =vlookup or offset or some calculations, with both absolute or relative references. i guess the point i want to make here is simply that i would like to copy the formula into another sheet keeping the same reference (formula in sheet1!A1 points to D5? i want to copy it into Sheet3!X3 so that it still points to Sheet1!D5 - note that the name of the tab was not included in the original formula). And i need to copy it, not modify the existing ones! The reason being that it is a cash flow model and i need to select an area with more than 1000 cells in it Hope this clarifies a bit more my need .. thanks! "Pete_UK" wrote: Give an example of the formulae which are causing problems. Pete On Aug 5, 11:01 am, Walter wrote: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want.. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#7
|
|||
|
|||
Copying formulas between sheets and keeping original ref
thanks very much Stefi, that might be a solution
how can i adjust the code to add to each reference in the formulas the name of the sheet? (problem is that some formulas are like =A5 some other point to =sheet2!a5 so i dont want to add it two times..) thanks again "Stefi" wrote: Then try a solution of this kind: Sub test() Cells(ActiveCell.Row, ActiveCell.Column + 1).Formula = _ "=" & Mid(ActiveCell.Formula, 2) End Sub This sub copies the formula in the active cell into the cell same row, next column, without changing the original references. Adjust it to your needs! Regards, Stefi „Walter” ezt *rta: I cant do that. "Stefi" wrote: Try to use absolute references! Regards, Stefi „Walter” ezt *rta: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#8
|
|||
|
|||
Copying formulas between sheets and keeping original ref
If the original formula contains sheet name then the result cell shall also
contain the same sheet name. Stefi „Walter” ezt *rta: thanks very much Stefi, that might be a solution how can i adjust the code to add to each reference in the formulas the name of the sheet? (problem is that some formulas are like =A5 some other point to =sheet2!a5 so i dont want to add it two times..) thanks again "Stefi" wrote: Then try a solution of this kind: Sub test() Cells(ActiveCell.Row, ActiveCell.Column + 1).Formula = _ "=" & Mid(ActiveCell.Formula, 2) End Sub This sub copies the formula in the active cell into the cell same row, next column, without changing the original references. Adjust it to your needs! Regards, Stefi „Walter” ezt *rta: I cant do that. "Stefi" wrote: Try to use absolute references! Regards, Stefi „Walter” ezt *rta: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
#9
|
|||
|
|||
Copying formulas between sheets and keeping original ref
Select the source cells
EditReplace What: = With: ^^^ Replace all Copy to destination sheet. Reverse the process on both sheets. Gord Dibben MS Excel MVP On Tue, 5 Aug 2008 03:49:01 -0700, Walter wrote: Hi Pete. Formulas are different, they might be easy like =A2 or more complicated like =vlookup or offset or some calculations, with both absolute or relative references. i guess the point i want to make here is simply that i would like to copy the formula into another sheet keeping the same reference (formula in sheet1!A1 points to D5? i want to copy it into Sheet3!X3 so that it still points to Sheet1!D5 - note that the name of the tab was not included in the original formula). And i need to copy it, not modify the existing ones! The reason being that it is a cash flow model and i need to select an area with more than 1000 cells in it Hope this clarifies a bit more my need .. thanks! "Pete_UK" wrote: Give an example of the formulae which are causing problems. Pete On Aug 5, 11:01 am, Walter wrote: Hi all I have been trying to copy some formulas from a tab to another in the same worksheet. what i want to do is to keep the original references of the formulas. If i do a simple copy and paste, the resulting formulas will point to different cells. One way i devised to overcome this was to 1) cut and paste the formulas into the other tab 2) change all formulas into strings 3) copy strings back to the original place 4) back from strings to formulas but this has the negative effect that all other cells linking to the ones i cut, will be re-referenced to the new ones, that is something i dont want.. I hope this is not too confusing, but is there any way to copy formulas and keep where they originally pointed to? many thanks for your help!! |
Thread Tools | |
Display Modes | |
|
|