| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Via code, making 2 named Ranges Indentical, upon change
Hi,
Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Firstly, I assume that when you say 'named ranges' you mean ranges for which
you have defined a name in the interactive mode and not that you have assigned a range to a variable in VBA. Secondly, I assume the the 2nd range is to match the first range. The problem here is with formulas. If a formula is copied from one area to another then the addresses are relative. If you have formulas, is this OK? As a starting point I have written the following change event macro. When a change is made within the first range, the second range is cleared and then the first range is copied to it and then the second range is renamed in case it has changed in size due to either inserting a row or deleting a row from the first range. If I have made incorrect assumptions and what I have done is not what you want then let me know and I will have another look at it. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Object 'Set isect = Application.Intersect(Target, Range("Range_1")) Set isect = Intersect(Target, Range("Range_1")) If Not isect Is Nothing Then 'Copy Range_1 to Range_2 Application.EnableEvents = False 'Clear data from Range_2 in case Range_1 is now smaller Range("Range_2").Clear Range("Range_1").Copy Range("Range_2").Select ActiveSheet.Paste 'Rename the selected area ActiveWorkbook.Names.Add Name:="Range_2", _ RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub -- Regards, OssieMac "ML0940" wrote: Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
See how you get on with this -
' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Hi Peter
Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
I don't understand what you are trying to cater for, doesn't make sense.
Explain with examples Regards, Peter T "ML0940" wrote in message ... Hi Peter Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
"I don't understand what you are trying to cater for, doesn't make sense.
Explain with examples " Hi Peter I'm not sure wha doesn't make sense? I have 2 named ranges, both is 2 seperate worksheets. Both are 19 rows and have the same information, that is strings of text. So, if I change one, I need the other one change as well, so that the both always remane identical Also, the names need to remain the same, no matter what. Does that make more sense? Thank you ML "Peter T" wrote: I don't understand what you are trying to cater for, doesn't make sense. Explain with examples Regards, Peter T "ML0940" wrote in message ... Hi Peter Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Do you mean you want two named ranges with the same name. You can do that
with Worksheet level names (aka Local names). 'Sheet1'!aaa 'Sheet2'!aaa Depending on the sheet name it might need to be embraced with apostrophes, programmatically always included them when naming a local name, even if not required as in the above examples. You could also have a Global name "aaa", but that would likely cause you problems as you would not be able to refer to it while on a sheet that has a similarly named Local name. Regards, Peter T "ML0940" wrote in message ... "I don't understand what you are trying to cater for, doesn't make sense. Explain with examples " Hi Peter I'm not sure wha doesn't make sense? I have 2 named ranges, both is 2 seperate worksheets. Both are 19 rows and have the same information, that is strings of text. So, if I change one, I need the other one change as well, so that the both always remane identical Also, the names need to remain the same, no matter what. Does that make more sense? Thank you ML "Peter T" wrote: I don't understand what you are trying to cater for, doesn't make sense. Explain with examples Regards, Peter T "ML0940" wrote in message ... Hi Peter Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Range_2 simply overwrites the previous range named Range_2. No new name is
required. Replace the range_1 and Range_2 with the names you have created.The reason for overwriting it is to adjust the named range to the new range if you have inserted or deleted a row. If the ranges are on different sheets, everywhere you address the ranges you need to select/activate the sheet first. Unfortunately I was not able to write it in a way that the ranges did not have to be selected for copy and paste because I needed the selection to get the new range to be named. -- Regards, OssieMac "ML0940" wrote: Hi Peter Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Ossie
That ssound good! I will give it a shot and get back. That is actually a clever idea. I noticed (like you said) that you use Paste, perhaps paste values, under paste special would be a better method? Well, one step at a time, let me go try your code Thank you ML "OssieMac" wrote: Range_2 simply overwrites the previous range named Range_2. No new name is required. Replace the range_1 and Range_2 with the names you have created.The reason for overwriting it is to adjust the named range to the new range if you have inserted or deleted a row. If the ranges are on different sheets, everywhere you address the ranges you need to select/activate the sheet first. Unfortunately I was not able to write it in a way that the ranges did not have to be selected for copy and paste because I needed the selection to get the new range to be named. -- Regards, OssieMac "ML0940" wrote: Hi Peter Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Ossie
This was my first crack At first it wort of worked, then on second try, nothing is worrking. I have the code in the Worksheet Change Module on Work Sheet 1 and there are only 2 work sheets that I am using. Each named range are on different sheets. So, here is what I've done so far: Any more insite is really appreciated Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) Dim Sh1bW As Range, Sh2bw As Range Dim isect As Object Set Sh1bW = Range("Sh1billsW") Set Sh2bw = Sheets(2).Range("Sh2billsW") 'Set isect = Application.Intersect(Target, Range("Range_1")) Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then 'Copy Range_1 to Range_2 Application.EnableEvents = False 'Clear data from Range_2 in case Range_1 is now smaller Sh2bw.Offset(0, 1).Clear Sh1bW.Copy Sh2bw.Offset(0, 1).Select 'ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone ActiveSheet.Paste 'Rename the selected area ' ActiveWorkbook.Names.Add Name:="Sh2billsW", _ ' RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub "OssieMac" wrote: Range_2 simply overwrites the previous range named Range_2. No new name is required. Replace the range_1 and Range_2 with the names you have created.The reason for overwriting it is to adjust the named range to the new range if you have inserted or deleted a row. If the ranges are on different sheets, everywhere you address the ranges you need to select/activate the sheet first. Unfortunately I was not able to write it in a way that the ranges did not have to be selected for copy and paste because I needed the selection to get the new range to be named. -- Regards, OssieMac "ML0940" wrote: Hi Peter Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
The amended code below works with 2 sheets.
By the way, if nothing is working it is possible that you had a code failure after the events were turned off and you have not turned them on again. You need a sub like the following and run it if the code fails after events turned off. Sub Reset_Events() Application.EnableEvents = True End Sub Following code works for me. I even got the paste special working. I have avoided assigning names in VBA to the ranges because the selection after the paste is required so that the area selected can be renamed. I will give it some more thought to see if there is a better way of doing it using assigned names for the ranges without selecting the sheets and ranges. Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Object Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then 'Copy Sh1billsW to Sh2billsW Application.EnableEvents = False 'Clear data from Sh2billsW in case Sh1billsW is smaller Sheets("Sheet2").Range("Sh2billsW").ClearContents Range("Sh1billsW").Copy 'Need to select sheet before you can 'select a range on the sheet Sheets("Sheet2").Select ActiveSheet.Range("Sh2billsW").Select Selection.PasteSpecial Paste:=xlPasteValues 'ActiveSheet.Paste 'alternative paste 'Rename the currently selected area ActiveWorkbook.Names.Add Name:="Sh2billsW", _ RefersToR1C1:=Selection Sheets("Sheet1").Select Application.CutCopyMode = False End If Application.EnableEvents = True End Sub -- Regards, OssieMac "ML0940" wrote: Ossie This was my first crack At first it wort of worked, then on second try, nothing is worrking. I have the code in the Worksheet Change Module on Work Sheet 1 and there are only 2 work sheets that I am using. Each named range are on different sheets. So, here is what I've done so far: Any more insite is really appreciated Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) Dim Sh1bW As Range, Sh2bw As Range Dim isect As Object Set Sh1bW = Range("Sh1billsW") Set Sh2bw = Sheets(2).Range("Sh2billsW") 'Set isect = Application.Intersect(Target, Range("Range_1")) Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then 'Copy Range_1 to Range_2 Application.EnableEvents = False 'Clear data from Range_2 in case Range_1 is now smaller Sh2bw.Offset(0, 1).Clear Sh1bW.Copy Sh2bw.Offset(0, 1).Select 'ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone ActiveSheet.Paste 'Rename the selected area ' ActiveWorkbook.Names.Add Name:="Sh2billsW", _ ' RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub "OssieMac" wrote: Range_2 simply overwrites the previous range named Range_2. No new name is required. Replace the range_1 and Range_2 with the names you have created.The reason for overwriting it is to adjust the named range to the new range if you have inserted or deleted a row. If the ranges are on different sheets, everywhere you address the ranges you need to select/activate the sheet first. Unfortunately I was not able to write it in a way that the ranges did not have to be selected for copy and paste because I needed the selection to get the new range to be named. -- Regards, OssieMac "ML0940" wrote: Hi Peter Than you again for taking the time All of your assunmptions were indeed correct. I think this is a clever way to handle it, except, I can not have a named range be replaced with another name, as other macros use those named ranges. Is it possiblle to delete and create it again with the same name? Perhaps you could do currrange = "exisitng named range" Then newrange = currrange Other then that, it sounds like a great idea ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Also,
My ranges are on two different worksheets ML "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Via code, making 2 named Ranges Indentical, upon change
Sorry
OssieMac's assumptions were all correct but I need to keep the same named range names "Peter T" wrote: See how you get on with this - ' in the ThisWorkbook module Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim b1 As Boolean, b2 As Boolean Dim r1 As Range, r2 As Range On Error Resume Next Set r1 = Range("aaa") Set r2 = Range("bbb") On Error GoTo errExit b1 = r1 Is Nothing And Not r2 Is Nothing If Not b1 Then b2 = r2 Is Nothing And Not r1 Is Nothing End If If b1 Or b2 Then Application.EnableEvents = False If b1 Then r2.ClearContents ElseIf b2 Then r1.ClearContents End If Else If Sh Is r1.Parent Then b1 = Not Intersect(Target, r1) Is Nothing End If If Not b1 Then If Sh Is r2.Parent Then b2 = Not Intersect(Target, r2) Is Nothing End If End If If b1 Or b2 Then On Error GoTo errExit Application.EnableEvents = False If b1 Then r2.Value = r1.Value Else r1.Value = r2.Value End If End If End If errExit: If b1 Or b2 Then Application.EnableEvents = True End Sub As written it might not cater for all your needs (eg entire deletion of rows/cols containing a named range) or not quite the way you want to (eg ranges not same size). If both ranges are on the same sheet it would be better to use the equivalent event in the sheet module (don't just paste the above into a sheet module, get the correct 'Change' event vs the above 'SheetChange', also remove the "If Sh Is r1.Parent Then" checks. Regards, Peter T "ML0940" wrote in message ... Hi, Here is a good one I have been pondering for a while, still, in all my efforts, I have not gotten what I really need. I am not an Excel-VBA expert, so I am hoping somebody in here can give some insite. OK, unforunately, there is no row insert/delete event..WISH LIST I have 2 seperate named ranges; if I change one, I would like the other to change and look exactly the same as the one that I just changed. That means, if I delete a row in one, then I want the other to delete a row and update all the values in the cells to match precisely. Same if I add a row. I hope this makes sense and is this possible? If may work on a Change event but it is not necessary really; if I change one, then I don't mind clicking a macro to update. Thanks in advance! ML |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
| building formulas that change frequently using named cell ranges | Excel Worksheet Functions | |||
| Change Named Cell ranges based upon value of another cell? | Excel Programming | |||
| Named ranges vs setting range in code | Excel Programming | |||
| Through code : Making a global change to 100 templates | Excel Programming | |||
Hybrid Mode
