$$Excel-Macros$$ Copying cells while keep cell references
Hi, I have a spreadsheet with large areas of cells that do not have anchors in the formuals. In other words, the content of a cell would for example show =A1 instead of =$A$1 I need to copy this block of cells to a another location (not move, I understand this works with cut/paste) where I would like to reuse it. However, when doing so, obviously Excel updates all the cell references. Is there a way to keep cell references through some trick (again, there are no anchors and I'm too lazy to go into every cell and achor the references)? I tried under copy/paste-value Paste Links but that didn't work because it just created a link to the cell where copied from. Not what I'm looking for. I believe I asked something similar but searched this form and couldn't find anything. My apologies if this was asked before. Thanks, Chris -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Copying cells while keep cell references
The main issue is where the references point. If they are pointing within the block you are copying, there is no issue as they will continue to point to the appropriate cells within the block. If they are to another sheet and the block is pasted in a different starting position (same or other sheet) there will be an issue which could be corrected by making the references absolute.If they are to an area outside the block on the same sheet and the block is pasted to a different sheet you will have problems anyhow. You will need to reconstruct those references to point to the old sheet, not the new one. If you are copying the block to a different position on the same sheet and the references are to areas outside the block on that sheet, then setting absolute references is needed. To convert all the formulas in a block to absolute you can use the following VBA Public Sub MyConvertFormulas() Dim oRange As Range Dim c as Range Set oRange = Selection for each c in oRange c.Formula = Application.ConvertFormula(Formula:=c.Formula, _ fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute) End Sub Regards David Grugeon On 17 May 2013 01:22, Chris christoph...@gmail.com wrote: Hi, I have a spreadsheet with large areas of cells that do not have anchors in the formuals. In other words, the content of a cell would for example show =A1 instead of =$A$1 I need to copy this block of cells to a another location (not move, I understand this works with cut/paste) where I would like to reuse it. However, when doing so, obviously Excel updates all the cell references. Is there a way to keep cell references through some trick (again, there are no anchors and I'm too lazy to go into every cell and achor the references)? I tried under copy/paste-value Paste Links but that didn't work because it just created a link to the cell where copied from. Not what I'm looking for. I believe I asked something similar but searched this form and couldn't find anything. My apologies if this was asked before. Thanks, Chris -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Copying cells while keep cell references
Sorry premature send! The code is Public Sub MyConvertFormulas() Dim oRange As Range Dim c as Range Set oRange = Selection for each c in oRange c.Formula = Application.ConvertFormula(Formula:=c.Formula, _ fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute) Next c End Sub Regards David Grugeon On 17 May 2013 07:44, David Grugeon da...@grugeon.com.au wrote: The main issue is where the references point. If they are pointing within the block you are copying, there is no issue as they will continue to point to the appropriate cells within the block. If they are to another sheet and the block is pasted in a different starting position (same or other sheet) there will be an issue which could be corrected by making the references absolute.If they are to an area outside the block on the same sheet and the block is pasted to a different sheet you will have problems anyhow. You will need to reconstruct those references to point to the old sheet, not the new one. If you are copying the block to a different position on the same sheet and the references are to areas outside the block on that sheet, then setting absolute references is needed. To convert all the formulas in a block to absolute you can use the following VBA Public Sub MyConvertFormulas() Dim oRange As Range Dim c as Range Set oRange = Selection for each c in oRange c.Formula = Application.ConvertFormula(Formula:=c.Formula, _ fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute) End Sub Regards David Grugeon On 17 May 2013 01:22, Chris christoph...@gmail.com wrote: Hi, I have a spreadsheet with large areas of cells that do not have anchors in the formuals. In other words, the content of a cell would for example show =A1 instead of =$A$1 I need to copy this block of cells to a another location (not move, I understand this works with cut/paste) where I would like to reuse it. However, when doing so, obviously Excel updates all the cell references. Is there a way to keep cell references through some trick (again, there are no anchors and I'm too lazy to go into every cell and achor the references)? I tried under copy/paste-value Paste Links but that didn't work because it just created a link to the cell where copied from. Not what I'm looking for. I believe I asked something similar but searched this form and couldn't find anything. My apologies if this was asked before. Thanks, Chris -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit
