Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
Hi,
Excel 2003, little knowledge. I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
On Jan 26, 11:44*am, donh wrote:
Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
On Jan 26, 2:30*pm, Alan wrote:
On Jan 26, 11:44*am, donh wrote: Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A.- Hide quoted text - - Show quoted text - Hi Alan, The way I've done it is the Range Forbidden is always active and lives on the worksheet it applies to not in a module and with my limited knowledge do not know how to turn it off and on if required Don |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
Do you want to copy the values to some other location? Or do you want
to just remove the formulas and replace those with actual values (returned by those formulas)? If so, you don't need to copy at all. You can have a loop that: - loops through every cell in the range of your interest (forbidenRange) - replaces the cell's .value with .value (i.e. it just puts its visible value back into the cell as a value [as oppose to formula]) that's it. On Jan 26, 2:40*pm, donh wrote: On Jan 26, 2:30*pm, Alan wrote: On Jan 26, 11:44*am, donh wrote: Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A.- Hide quoted text - - Show quoted text - Hi Alan, The way I've done it is the Range Forbidden is always active and lives on the worksheet it applies to not in a module and with my limited knowledge do not know how to turn it off and on if required Don- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
On Jan 26, 2:40*pm, donh wrote:
On Jan 26, 2:30*pm, Alan wrote: On Jan 26, 11:44*am, donh wrote: Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A.- Hide quoted text - - Show quoted text - Hi Alan, The way I've done it is the Range Forbidden is always active and lives on the worksheet it applies to not in a module and with my limited knowledge do not know how to turn it off and on if required Don- Hide quoted text - - Show quoted text - AB is right. You may not be able to select the cells but there is nothing to stop you using code to change cell contects without selectiing them. Alternatively, if that is too complicated and you are looking to make a one off change, the code you have shown us is a worksheet event. Comment this code out to stop it running, make your copy/paste changes manually and then reacticate the code. OR you could bypass the event code by using a password set elsewhe Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range If Range("A1") = "MyPassword" Then Exit Sub Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Remove your password and the code comes back into play. A |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
On Jan 26, 3:15*pm, Alan wrote:
On Jan 26, 2:40*pm, donh wrote: On Jan 26, 2:30*pm, Alan wrote: On Jan 26, 11:44*am, donh wrote: Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A.- Hide quoted text - - Show quoted text - Hi Alan, The way I've done it is the Range Forbidden is always active and lives on the worksheet it applies to not in a module and with my limited knowledge do not know how to turn it off and on if required Don- Hide quoted text - - Show quoted text - AB is right. You may not be able to select the cells but there is nothing to stop you using code to change cell contects without selectiing them. Alternatively, if that is too complicated and you are looking to make a one off change, the code you have shown us is a worksheet event. Comment this code out to stop it running, make your copy/paste changes manually and then reacticate the code. OR you could bypass the event code by using a password set elsewhe Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range If Range("A1") = "MyPassword" Then Exit Sub Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Remove your password and the code comes back into play. A- Hide quoted text - - Show quoted text - Thank you both for replying. The workbook will not be under my control so I need to keep things up front rather than expecting the user to dive into the VBA so like the password idea for unlocking. At the same time this would be a one-time overwrite of formulas to values so would be happy to incorporate a loop, just don't know how. Thanks Don |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
On Jan 26, 3:43*pm, donh wrote:
On Jan 26, 3:15*pm, Alan wrote: On Jan 26, 2:40*pm, donh wrote: On Jan 26, 2:30*pm, Alan wrote: On Jan 26, 11:44*am, donh wrote: Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A.- Hide quoted text - - Show quoted text - Hi Alan, The way I've done it is the Range Forbidden is always active and lives on the worksheet it applies to not in a module and with my limited knowledge do not know how to turn it off and on if required Don- Hide quoted text - - Show quoted text - AB is right. You may not be able to select the cells but there is nothing to stop you using code to change cell contects without selectiing them. Alternatively, if that is too complicated and you are looking to make a one off change, the code you have shown us is a worksheet event. Comment this code out to stop it running, make your copy/paste changes manually and then reacticate the code. OR you could bypass the event code by using a password set elsewhe Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range If Range("A1") = "MyPassword" Then Exit Sub Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Remove your password and the code comes back into play. A- Hide quoted text - - Show quoted text - Thank you both for replying. *The workbook will not be under my control so I need to keep things up front rather than expecting the user to dive into the VBA so like the password idea for unlocking. *At the same time this would be a one-time overwrite of formulas to values so would be happy to incorporate a loop, just don't know how. Thanks Don- Hide quoted text - - Show quoted text - Don So far we have called it a loop because we have assumed that you may only want to get rid of the formulae in specific cells. If you want to clear the whole sheet it is simpler than a loop. Add the following macro. Sub RemoveFormulae() Cells.Copy Range("A1").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Range("A1").Select End Sub This does, using code, what I think you were suggesting you were wanting to do manually ... but it does change the whole sheet. You may still like to use the password idea so that you can check that it has done what you wanted it to do after execution. A. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
On Jan 27, 8:24*am, Alan wrote:
On Jan 26, 3:43*pm, donh wrote: On Jan 26, 3:15*pm, Alan wrote: On Jan 26, 2:40*pm, donh wrote: On Jan 26, 2:30*pm, Alan wrote: On Jan 26, 11:44*am, donh wrote: Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A.- Hide quoted text - - Show quoted text - Hi Alan, The way I've done it is the Range Forbidden is always active and lives on the worksheet it applies to not in a module and with my limited knowledge do not know how to turn it off and on if required Don- Hide quoted text - - Show quoted text - AB is right. You may not be able to select the cells but there is nothing to stop you using code to change cell contects without selectiing them. Alternatively, if that is too complicated and you are looking to make a one off change, the code you have shown us is a worksheet event. Comment this code out to stop it running, make your copy/paste changes manually and then reacticate the code. OR you could bypass the event code by using a password set elsewhe Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range If Range("A1") = "MyPassword" Then Exit Sub Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Remove your password and the code comes back into play. A- Hide quoted text - - Show quoted text - Thank you both for replying. *The workbook will not be under my control so I need to keep things up front rather than expecting the user to dive into the VBA so like the password idea for unlocking. *At the same time this would be a one-time overwrite of formulas to values so would be happy to incorporate a loop, just don't know how. Thanks Don- Hide quoted text - - Show quoted text - Don So far we have called it a loop because we have assumed that you may only want to get rid of the formulae in specific cells. If you want to clear the whole sheet it is simpler than a loop. Add the following macro. Sub RemoveFormulae() * * Cells.Copy * * Range("A1").PasteSpecial Paste:=xlPasteValues * * Application.CutCopyMode = False * * Range("A1").Select End Sub This does, using code, what I think you were suggesting you were wanting to do manually ... but it does change the whole sheet. You may still like to use the password idea so that you can check that it has done what you wanted it to do after execution. A.- Hide quoted text - - Show quoted text - Alan, Thank you that does exactly want I wanted. Many thanks Don |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying all whilst Range Forbidden active
On Jan 27, 8:47*am, donh wrote:
On Jan 27, 8:24*am, Alan wrote: On Jan 26, 3:43*pm, donh wrote: On Jan 26, 3:15*pm, Alan wrote: On Jan 26, 2:40*pm, donh wrote: On Jan 26, 2:30*pm, Alan wrote: On Jan 26, 11:44*am, donh wrote: Hi, Excel 2003, little knowledge. *I'm using VBA below to protect my worksheet as I have found that using the standard Protect Cells stops me from using a scroll bar on the page. *I have now gone a little further and wanted to Copy All and Paste Values to remove all formulas so the page can be archived and outside refernce tables be changed without affecting past information. *Of course I've now hit the problem that I cant Copy All because I have the Range Forbidden Active! *anu suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Many thanks Don Can you not copy/paste before you set the forbidden range? A.- Hide quoted text - - Show quoted text - Hi Alan, The way I've done it is the Range Forbidden is always active and lives on the worksheet it applies to not in a module and with my limited knowledge do not know how to turn it off and on if required Don- Hide quoted text - - Show quoted text - AB is right. You may not be able to select the cells but there is nothing to stop you using code to change cell contects without selectiing them. Alternatively, if that is too complicated and you are looking to make a one off change, the code you have shown us is a worksheet event. Comment this code out to stop it running, make your copy/paste changes manually and then reacticate the code. OR you could bypass the event code by using a password set elsewhe Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rgForbidden As Range If Range("A1") = "MyPassword" Then Exit Sub Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), Range("B106:R110"), Range("T10:U56")) If Intersect(Target, rgForbidden) Is Nothing Then Exit Sub Range("A1").Select End Sub Remove your password and the code comes back into play. A- Hide quoted text - - Show quoted text - Thank you both for replying. *The workbook will not be under my control so I need to keep things up front rather than expecting the user to dive into the VBA so like the password idea for unlocking. *At the same time this would be a one-time overwrite of formulas to values so would be happy to incorporate a loop, just don't know how. Thanks Don- Hide quoted text - - Show quoted text - Don So far we have called it a loop because we have assumed that you may only want to get rid of the formulae in specific cells. If you want to clear the whole sheet it is simpler than a loop. Add the following macro. Sub RemoveFormulae() * * Cells.Copy * * Range("A1").PasteSpecial Paste:=xlPasteValues * * Application.CutCopyMode = False * * Range("A1").Select End Sub This does, using code, what I think you were suggesting you were wanting to do manually ... but it does change the whole sheet. You may still like to use the password idea so that you can check that it has done what you wanted it to do after execution. A.- Hide quoted text - - Show quoted text - Alan, Thank you that does exactly want I wanted. Many thanks Don- Hide quoted text - - Show quoted text - Glad to have been of help. Just for completeness, if you had wanted to "loop" through individual cells you could have used ... Sub RemoveFormulaeFromCells() Dim rgForbidden As Range Dim Cell As Range Set rgForbidden = Union(Range("A2:D56"), Range("C2:F2"), _ Range("E8:U9"), Range("E10:E56"), Range("G10:G56"), Range("I10:I56"), _ Range("K10:K56"), _ Range("M10:M56"), Range("O10:O56"), Range("Q10:Q56"), _ Range("W1:BC200"), Range("B58:R62"), Range("B66:R70"), _ Range("B66:R70"), Range("B74:R78"), _ Range("B82:R86"), Range("B90:R94"), Range("B98:R102"), _ Range("B106:R110"), Range("T10:U56")) Application.ScreenUpdating = False For Each Cell In rgForbidden Cell.Copy Cell.PasteSpecial Paste:=xlPasteValues Next Application.CutCopyMode = False Range("A1").Select Application.ScreenUpdating = True End Sub .... This only changes the formulae in your named ranges but due to the number of iterations it does take longer to run so I wouldn't advise using it. A. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Site for some reason, and forbidden | Excel Worksheet Functions | |||
getting the selected range and active cell of a non active worksheetsheet | Excel Programming | |||
Getting Range whilst looping through files in directory | Excel Programming | |||
easy way to check for forbidden characters? | Excel Programming | |||
MACRO for copying active sheet without using a certain name | Excel Programming |