Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi,
With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi again,
The code you have posted is not the same code I gave you. While I also like to resist selecting the ranges in the code for copying and pasting, I was not able to do what you wanted without copying the first named range then selecting the second named range and then pasting. After Pasting in this way, the correct range is already selected from the paste for renaming. Also, originally when I posted a reply for you I thought that your ranges were on the same worksheet. You replied that they were on different sheets and I amended the code for you so that it worked with the ranges on separate sheets. Now you say you have two named ranges on ONE worksheet. The following is my code as previously posted using separate worksheets for each range. I tested it extensively and I believe it works. If you want it on one worksheet, edit the following line:- Sheets("Sheet2").Range("Sh2billsW").ClearContents and change it to the following:- Range("Sh2billsW").ClearContents Leave out the following line:- Sheets("Sheet2").Select 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 '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: Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi Ossie
I don't think that you read my last post, in the original post. Yes, I am using two ranges in two seperate worksheets but it is easier to explain on the forum this way. If I get the answer I need, then I can then easily migrate the answer to two seperate worksheets The reason that I changed the code is because this method is much better then copying and pasting; did you try it? In the other post, I was explaining that the paste method is no good, as if there are cells below the pasted range, there will be a conflict, if there is overlap. Therefore, when a row is inserted into the first range, the second range must also have a additional row added to it and shift the rows down by 1. Again, I appreciate your code very much, as it did get me on the right track. oy said also that you tested it extensively, howver, after a few tests, I quickly realized that it was not quite right ML "OssieMac" wrote: Hi again, The code you have posted is not the same code I gave you. While I also like to resist selecting the ranges in the code for copying and pasting, I was not able to do what you wanted without copying the first named range then selecting the second named range and then pasting. After Pasting in this way, the correct range is already selected from the paste for renaming. Also, originally when I posted a reply for you I thought that your ranges were on the same worksheet. You replied that they were on different sheets and I amended the code for you so that it worked with the ranges on separate sheets. Now you say you have two named ranges on ONE worksheet. The following is my code as previously posted using separate worksheets for each range. I tested it extensively and I believe it works. If you want it on one worksheet, edit the following line:- Sheets("Sheet2").Range("Sh2billsW").ClearContents and change it to the following:- Range("Sh2billsW").ClearContents Leave out the following line:- Sheets("Sheet2").Select 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 '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: Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
OK. Now I think that I understand what you want to do. Previously I did not
understand that the second range needed to have rows inserted/deleted as per the first range to keep other data in the correct place on the worksheet. I also assumed that you were only inserting rows within the range and not an entire row across the worksheet. Is my assumption correct? I have an idea in my mind whereby rows can be inserted/deleted in the second range to match the first range and ultimately achieve what you want but I need to write the code and test it. It is 8pm in Aussieland at the moment and I have another priority tonight so I will await your answer to the above question and try to get back to you again tomorrow. -- Regards, OssieMac "ML0940" wrote: Hi Ossie I don't think that you read my last post, in the original post. Yes, I am using two ranges in two seperate worksheets but it is easier to explain on the forum this way. If I get the answer I need, then I can then easily migrate the answer to two seperate worksheets The reason that I changed the code is because this method is much better then copying and pasting; did you try it? In the other post, I was explaining that the paste method is no good, as if there are cells below the pasted range, there will be a conflict, if there is overlap. Therefore, when a row is inserted into the first range, the second range must also have a additional row added to it and shift the rows down by 1. Again, I appreciate your code very much, as it did get me on the right track. oy said also that you tested it extensively, howver, after a few tests, I quickly realized that it was not quite right ML "OssieMac" wrote: Hi again, The code you have posted is not the same code I gave you. While I also like to resist selecting the ranges in the code for copying and pasting, I was not able to do what you wanted without copying the first named range then selecting the second named range and then pasting. After Pasting in this way, the correct range is already selected from the paste for renaming. Also, originally when I posted a reply for you I thought that your ranges were on the same worksheet. You replied that they were on different sheets and I amended the code for you so that it worked with the ranges on separate sheets. Now you say you have two named ranges on ONE worksheet. The following is my code as previously posted using separate worksheets for each range. I tested it extensively and I believe it works. If you want it on one worksheet, edit the following line:- Sheets("Sheet2").Range("Sh2billsW").ClearContents and change it to the following:- Range("Sh2billsW").ClearContents Leave out the following line:- Sheets("Sheet2").Select 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 '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: Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi Ossie
So, you are in Australia? Wow! That is quite a distance from The Eastern US. Ossie, yes, you are absolutely, totally correct on your assumptions. I was trying to ask this from day one; as hard as we try, it is not often easy to put into words precisely what we are looking for. Also, all of the code you supplied was great, I just like the way I am doing this line Range("Sh2BWTest").Value = Range("Sh1BWTest").Value far better then the copy/paste method. Ossie, also the code that we were originally using, minus the copy/paste part and replaced with the above method, is still the same, I was just using this code as a test run. It is ok to also use this code as well, for the sake of testing; this way both ranges are on one worksheet, for visual. Then if we get it working properly, we can then make it work on 2 seperate worksheets. The last thing that I would like to note is this: If we use Sheets("Sheet2"), then there is an error that range is out of subscript, whereas, if we access the sheet like this Sheets(2) we are fine. That is likely because, my sheets have unique names and the code will not fine a sheet named "Sheet1" or "Sheet2", therefore, in code, I will often access the sheet in its order in the sheet collection, as opposed to name. eg Sheets(2) So, again, all your assumptions were correct and I look forward to your next reply. Thank you! ML :) "OssieMac" wrote: OK. Now I think that I understand what you want to do. Previously I did not understand that the second range needed to have rows inserted/deleted as per the first range to keep other data in the correct place on the worksheet. I also assumed that you were only inserting rows within the range and not an entire row across the worksheet. Is my assumption correct? I have an idea in my mind whereby rows can be inserted/deleted in the second range to match the first range and ultimately achieve what you want but I need to write the code and test it. It is 8pm in Aussieland at the moment and I have another priority tonight so I will await your answer to the above question and try to get back to you again tomorrow. -- Regards, OssieMac "ML0940" wrote: Hi Ossie I don't think that you read my last post, in the original post. Yes, I am using two ranges in two seperate worksheets but it is easier to explain on the forum this way. If I get the answer I need, then I can then easily migrate the answer to two seperate worksheets The reason that I changed the code is because this method is much better then copying and pasting; did you try it? In the other post, I was explaining that the paste method is no good, as if there are cells below the pasted range, there will be a conflict, if there is overlap. Therefore, when a row is inserted into the first range, the second range must also have a additional row added to it and shift the rows down by 1. Again, I appreciate your code very much, as it did get me on the right track. oy said also that you tested it extensively, howver, after a few tests, I quickly realized that it was not quite right ML "OssieMac" wrote: Hi again, The code you have posted is not the same code I gave you. While I also like to resist selecting the ranges in the code for copying and pasting, I was not able to do what you wanted without copying the first named range then selecting the second named range and then pasting. After Pasting in this way, the correct range is already selected from the paste for renaming. Also, originally when I posted a reply for you I thought that your ranges were on the same worksheet. You replied that they were on different sheets and I amended the code for you so that it worked with the ranges on separate sheets. Now you say you have two named ranges on ONE worksheet. The following is my code as previously posted using separate worksheets for each range. I tested it extensively and I believe it works. If you want it on one worksheet, edit the following line:- Sheets("Sheet2").Range("Sh2billsW").ClearContents and change it to the following:- Range("Sh2billsW").ClearContents Leave out the following line:- Sheets("Sheet2").Select 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 '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: Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Ossie
Yes, again, Both assumptions: OK. Now I think that I understand what you want to do. Previously I did not understand that the second range needed to have rows inserted/deleted as per the first range to keep other data in the correct place on the worksheet. I also assumed that you were only inserting rows within the range and not an entire row across the worksheet. Is my assumption correct? Are correct. I do insert rows occasionally in other places on the worksheet but I am looking to have these two ranges adjust programtically. I am not trying to throw another curve ball at you, but I actually have a third range as well, that holds the exact same data as the other 2. I suppose if we can get the second one to work, then I can get a third one to also work, correct? That is update, if the first range is changed. The other thing that wouuld be good is if I changed any one of these 3 identical ranges, if the other 2 updated accordingly. Again, once we get the code working, I can handle that with conditional statements, I'm sure. Thanks again! ML "ML0940" wrote: Hi Ossie So, you are in Australia? Wow! That is quite a distance from The Eastern US. Ossie, yes, you are absolutely, totally correct on your assumptions. I was trying to ask this from day one; as hard as we try, it is not often easy to put into words precisely what we are looking for. Also, all of the code you supplied was great, I just like the way I am doing this line Range("Sh2BWTest").Value = Range("Sh1BWTest").Value far better then the copy/paste method. Ossie, also the code that we were originally using, minus the copy/paste part and replaced with the above method, is still the same, I was just using this code as a test run. It is ok to also use this code as well, for the sake of testing; this way both ranges are on one worksheet, for visual. Then if we get it working properly, we can then make it work on 2 seperate worksheets. The last thing that I would like to note is this: If we use Sheets("Sheet2"), then there is an error that range is out of subscript, whereas, if we access the sheet like this Sheets(2) we are fine. That is likely because, my sheets have unique names and the code will not fine a sheet named "Sheet1" or "Sheet2", therefore, in code, I will often access the sheet in its order in the sheet collection, as opposed to name. eg Sheets(2) So, again, all your assumptions were correct and I look forward to your next reply. Thank you! ML :) "OssieMac" wrote: OK. Now I think that I understand what you want to do. Previously I did not understand that the second range needed to have rows inserted/deleted as per the first range to keep other data in the correct place on the worksheet. I also assumed that you were only inserting rows within the range and not an entire row across the worksheet. Is my assumption correct? I have an idea in my mind whereby rows can be inserted/deleted in the second range to match the first range and ultimately achieve what you want but I need to write the code and test it. It is 8pm in Aussieland at the moment and I have another priority tonight so I will await your answer to the above question and try to get back to you again tomorrow. -- Regards, OssieMac "ML0940" wrote: Hi Ossie I don't think that you read my last post, in the original post. Yes, I am using two ranges in two seperate worksheets but it is easier to explain on the forum this way. If I get the answer I need, then I can then easily migrate the answer to two seperate worksheets The reason that I changed the code is because this method is much better then copying and pasting; did you try it? In the other post, I was explaining that the paste method is no good, as if there are cells below the pasted range, there will be a conflict, if there is overlap. Therefore, when a row is inserted into the first range, the second range must also have a additional row added to it and shift the rows down by 1. Again, I appreciate your code very much, as it did get me on the right track. oy said also that you tested it extensively, howver, after a few tests, I quickly realized that it was not quite right ML "OssieMac" wrote: Hi again, The code you have posted is not the same code I gave you. While I also like to resist selecting the ranges in the code for copying and pasting, I was not able to do what you wanted without copying the first named range then selecting the second named range and then pasting. After Pasting in this way, the correct range is already selected from the paste for renaming. Also, originally when I posted a reply for you I thought that your ranges were on the same worksheet. You replied that they were on different sheets and I amended the code for you so that it worked with the ranges on separate sheets. Now you say you have two named ranges on ONE worksheet. The following is my code as previously posted using separate worksheets for each range. I tested it extensively and I believe it works. If you want it on one worksheet, edit the following line:- Sheets("Sheet2").Range("Sh2billsW").ClearContents and change it to the following:- Range("Sh2billsW").ClearContents Leave out the following line:- Sheets("Sheet2").Select 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 '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: Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi again,
Hope that the following helps. I have coded it to work with the named ranges on separate sheets because you said that is your ultimate aim. You need to understand that if you select the first row of a named range and insert a row then the inserted row will be outside the named range. If you need to do that then the macro will not do what you want. For this reason, I have used the second row of the range to start any insert/delete rows in the second named range. Also, because events are turned off during the processing and do not get turned on again until the end of the sub, if for any reason the macro is interrupted by error or otherwise, then you need to run a special macro to turn the events back on again otherwise the events driven macros will not run. I usually insert the following macro and just run it from the VBA editor:- Sub Reset_Events() Application.EnableEvents = True End Sub Of course you will need to change all of the Sheet1 and Sheet2 names to match your sheet names or use the sheet index as you previously mentioned. If you want to run the macro to update multiple named ranges on different worksheets irrespective of what range is changed, then it needs to be inserted under ThisWorkbook module. I have inserted some examples at the bottom of this post to help you get started on this part by identifying which worksheet was changed. The modified code:- Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Application.EnableEvents = False 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if number rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged 'Case 0 for testing purposes only and 'can be deleted when testing finished MsgBox "No rows inserted or deleted" Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 .Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown, _ CopyOrigin:=xlFormatFromLeftOrAbove End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data Sheets("Sheet1").Range("Sh1billsW").Copy _ Destination:=Sheets("Sheet2").Range("Sh2billsW") End If Application.EnableEvents = True End Sub The following examples give 3 methods of identifying the worksheet that has been changed when the code is inserted in ThisWorkbook module:- Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'Identify sheet by sheet given name Select Case Sh.Name Case "Sheet1" MsgBox "Sheet1 changed" Case "Sheet2" MsgBox "Sheet2 changed" End Select 'Identify sheet by sheet index Select Case Sh.Index Case 1 MsgBox "Sheet index 1 changed" Case 2 MsgBox "Sheet index 2 changed" End Select 'Identify sheet by code name 'As per name in VBA Project explorer. (The name NOT in brackets) 'This name does not change when you manually change the name of a worksheet Select Case Sh.CodeName Case "Sheet1" MsgBox "Code name Sheet1 changed" Case "Sheet2" MsgBox "Code name Sheet2 changed" End Select End Sub -- Regards, OssieMac "ML0940" wrote: Ossie Yes, again, Both assumptions: OK. Now I think that I understand what you want to do. Previously I did not understand that the second range needed to have rows inserted/deleted as per the first range to keep other data in the correct place on the worksheet. I also assumed that you were only inserting rows within the range and not an entire row across the worksheet. Is my assumption correct? Are correct. I do insert rows occasionally in other places on the worksheet but I am looking to have these two ranges adjust programtically. I am not trying to throw another curve ball at you, but I actually have a third range as well, that holds the exact same data as the other 2. I suppose if we can get the second one to work, then I can get a third one to also work, correct? That is update, if the first range is changed. The other thing that wouuld be good is if I changed any one of these 3 identical ranges, if the other 2 updated accordingly. Again, once we get the code working, I can handle that with conditional statements, I'm sure. Thanks again! ML "ML0940" wrote: Hi Ossie So, you are in Australia? Wow! That is quite a distance from The Eastern US. Ossie, yes, you are absolutely, totally correct on your assumptions. I was trying to ask this from day one; as hard as we try, it is not often easy to put into words precisely what we are looking for. Also, all of the code you supplied was great, I just like the way I am doing this line Range("Sh2BWTest").Value = Range("Sh1BWTest").Value far better then the copy/paste method. Ossie, also the code that we were originally using, minus the copy/paste part and replaced with the above method, is still the same, I was just using this code as a test run. It is ok to also use this code as well, for the sake of testing; this way both ranges are on one worksheet, for visual. Then if we get it working properly, we can then make it work on 2 seperate worksheets. The last thing that I would like to note is this: If we use Sheets("Sheet2"), then there is an error that range is out of subscript, whereas, if we access the sheet like this Sheets(2) we are fine. That is likely because, my sheets have unique names and the code will not fine a sheet named "Sheet1" or "Sheet2", therefore, in code, I will often access the sheet in its order in the sheet collection, as opposed to name. eg Sheets(2) So, again, all your assumptions were correct and I look forward to your next reply. Thank you! ML :) "OssieMac" wrote: OK. Now I think that I understand what you want to do. Previously I did not understand that the second range needed to have rows inserted/deleted as per the first range to keep other data in the correct place on the worksheet. I also assumed that you were only inserting rows within the range and not an entire row across the worksheet. Is my assumption correct? I have an idea in my mind whereby rows can be inserted/deleted in the second range to match the first range and ultimately achieve what you want but I need to write the code and test it. It is 8pm in Aussieland at the moment and I have another priority tonight so I will await your answer to the above question and try to get back to you again tomorrow. -- Regards, OssieMac "ML0940" wrote: Hi Ossie I don't think that you read my last post, in the original post. Yes, I am using two ranges in two seperate worksheets but it is easier to explain on the forum this way. If I get the answer I need, then I can then easily migrate the answer to two seperate worksheets The reason that I changed the code is because this method is much better then copying and pasting; did you try it? In the other post, I was explaining that the paste method is no good, as if there are cells below the pasted range, there will be a conflict, if there is overlap. Therefore, when a row is inserted into the first range, the second range must also have a additional row added to it and shift the rows down by 1. Again, I appreciate your code very much, as it did get me on the right track. oy said also that you tested it extensively, howver, after a few tests, I quickly realized that it was not quite right ML "OssieMac" wrote: Hi again, The code you have posted is not the same code I gave you. While I also like to resist selecting the ranges in the code for copying and pasting, I was not able to do what you wanted without copying the first named range then selecting the second named range and then pasting. After Pasting in this way, the correct range is already selected from the paste for renaming. Also, originally when I posted a reply for you I thought that your ranges were on the same worksheet. You replied that they were on different sheets and I amended the code for you so that it worked with the ranges on separate sheets. Now you say you have two named ranges on ONE worksheet. The following is my code as previously posted using separate worksheets for each range. I tested it extensively and I believe it works. If you want it on one worksheet, edit the following line:- Sheets("Sheet2").Range("Sh2billsW").ClearContents and change it to the following:- Range("Sh2billsW").ClearContents Leave out the following line:- Sheets("Sheet2").Select 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 '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: Hi, With some help from user OssieMac, I was able to get the code to this point, but I think I may need some help to get to the next step. For the sake of this test, I have two named ranges on one worksheet: Range("Sh1BWTest") and Range("Sh2BWTest") When I insert a row into Range("Sh1BWTest"), I need the row range in Range("Sh2BWTest") to also adjust and update.. The code is almost there, I just can not get that last part. Any help would be appreciated. Thank you ML Private Sub Worksheet_Change(ByVal Target As Range) '*** This part works well Dim isect As Object Set isect = Intersect(Target, Range("Sh1BWTest")) If Not isect Is Nothing Then Application.EnableEvents = False 'Clear data from Sh2bWTest in case the Sh1BWTest range is smaller Range("Sh2BWTest").ClearContents MsgBox "Cleared" 'Confirm the clear was sucessful Range("Sh2BWTest").Value = Range("Sh1BWTest").Value 'Select and rename range Range("Sh2BWTest").Select ActiveWorkbook.Names.Add Name:="Sh2BWTest", _ RefersToR1C1:=Selection End If Application.EnableEvents = True 'Range("A1").Activate End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
My apologies. Further testing of the macro has shown up problems trying to
copy and paste and/or trying to make a named range = another named range. Basically the second named range is not getting updated with the new range when rows are inserted/deleted. I cannot get the macro to work properly without select-copy- select destination-paste. The reason is that the pasted area becomes the new selection that can be renamed. In the interactive mode, inserting and deleting rows in a named range alters the range for the named range. I intended doing this in the code but the named range is not getting updated with the new range when the insert or delete is done via a VBA. You need to understand that while I completely agree with the principle of copy/paste without selecting the ranges; in this case the selection is required because I don't know of any other way of identifying the range to update the named range. Amended code below. Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Application.EnableEvents = False 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if numnber rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged MsgBox "No rows inserted or deleted" 'Case 0 for testing purposes only and 'can be deleted when testing finished Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 .Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .ClearContents .Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data Sheets("Sheet1").Range("Sh1billsW").Copy Sheets("Sheet2").Select Sheets("Sheet2").Range("Sh2billsW").Select ActiveSheet.Paste ActiveWorkbook.Names.Add Name:="Sh2billsW", _ RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub -- Regards, OssieMac |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi Ossie
I really do appreciate the code and examples I will copy them now and give them a try. I have ben also helping fellow AutoCAD users with VBA today as well. My whole life is VBA LOL No, it isn't, but a lot of it is :) From looking at the code, it is interesting to see how you are handling it but I need to run it to see tht I fully understand what is going on. Ossie, I don't have any problem with the pasting, as long as pasting doesn't affect anything else like in the original posts. If the paste works, then I will switch itto paste special by values again; assuming that works. The reason I like the paste special by values is because on different worksheets I may have different size fonts etc; so if I just do paste, then it carries over the format as well, whereas paste special by values will just carry over the values and not the formatting. Ok, let me go try and get back to you. Thank you again! ML PS: Why don't they just have insert events? :) "OssieMac" wrote: My apologies. Further testing of the macro has shown up problems trying to copy and paste and/or trying to make a named range = another named range. Basically the second named range is not getting updated with the new range when rows are inserted/deleted. I cannot get the macro to work properly without select-copy- select destination-paste. The reason is that the pasted area becomes the new selection that can be renamed. In the interactive mode, inserting and deleting rows in a named range alters the range for the named range. I intended doing this in the code but the named range is not getting updated with the new range when the insert or delete is done via a VBA. You need to understand that while I completely agree with the principle of copy/paste without selecting the ranges; in this case the selection is required because I don't know of any other way of identifying the range to update the named range. Amended code below. Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Application.EnableEvents = False 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if numnber rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged MsgBox "No rows inserted or deleted" 'Case 0 for testing purposes only and 'can be deleted when testing finished Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 .Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .ClearContents .Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data Sheets("Sheet1").Range("Sh1billsW").Copy Sheets("Sheet2").Select Sheets("Sheet2").Range("Sh2billsW").Select ActiveSheet.Paste ActiveWorkbook.Names.Add Name:="Sh2billsW", _ RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi Ossie
I had the same problem. It is pasting the first range to Sheet 2, then I am getting an error, can not change part of a merged cell and the code is erroring out on the pate part. That immediately tell ms that the row in the second range is not being inserted prior to the paste, otherwise there would have been a new row to paste to. Back to the drawing board :) Mark "ML0940" wrote: Hi Ossie I really do appreciate the code and examples I will copy them now and give them a try. I have ben also helping fellow AutoCAD users with VBA today as well. My whole life is VBA LOL No, it isn't, but a lot of it is :) From looking at the code, it is interesting to see how you are handling it but I need to run it to see tht I fully understand what is going on. Ossie, I don't have any problem with the pasting, as long as pasting doesn't affect anything else like in the original posts. If the paste works, then I will switch itto paste special by values again; assuming that works. The reason I like the paste special by values is because on different worksheets I may have different size fonts etc; so if I just do paste, then it carries over the format as well, whereas paste special by values will just carry over the values and not the formatting. Ok, let me go try and get back to you. Thank you again! ML PS: Why don't they just have insert events? :) "OssieMac" wrote: My apologies. Further testing of the macro has shown up problems trying to copy and paste and/or trying to make a named range = another named range. Basically the second named range is not getting updated with the new range when rows are inserted/deleted. I cannot get the macro to work properly without select-copy- select destination-paste. The reason is that the pasted area becomes the new selection that can be renamed. In the interactive mode, inserting and deleting rows in a named range alters the range for the named range. I intended doing this in the code but the named range is not getting updated with the new range when the insert or delete is done via a VBA. You need to understand that while I completely agree with the principle of copy/paste without selecting the ranges; in this case the selection is required because I don't know of any other way of identifying the range to update the named range. Amended code below. Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Application.EnableEvents = False 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if numnber rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged MsgBox "No rows inserted or deleted" 'Case 0 for testing purposes only and 'can be deleted when testing finished Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 .Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .ClearContents .Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data Sheets("Sheet1").Range("Sh1billsW").Copy Sheets("Sheet2").Select Sheets("Sheet2").Range("Sh2billsW").Select ActiveSheet.Paste ActiveWorkbook.Names.Add Name:="Sh2billsW", _ RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub -- Regards, OssieMac |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Ossie
Here are my findings. First of all, you are doing a great job because I would not have gotten this far along without your help and it is working pretty darn good now. Ok, On worksheet 2, if there is a blank row just below the last row in the named range, then the macro works perfect. If there is data in the row directly below the last row in the named range, it is getting overwritten. Then explains the first problem, as I have a merged cell just below the range on Worksheet 2. So, it looks like a row is being added to the range, but not to worksheet 2. if we insert a row into the first range manually, of course it will work but we need to also insert that row in Worksheet 2. It isn't enough to just paste. To demonstrate what I am explaining, put some text into the cell directly below range 2, then in The code, add On Error Resume Next, above ActiveSheet.Paste You will then seehow range 2 is growing but no new cells are being added to the collection in Worksheet 2 Thank you Mark "OssieMac" wrote: My apologies. Further testing of the macro has shown up problems trying to copy and paste and/or trying to make a named range = another named range. Basically the second named range is not getting updated with the new range when rows are inserted/deleted. I cannot get the macro to work properly without select-copy- select destination-paste. The reason is that the pasted area becomes the new selection that can be renamed. In the interactive mode, inserting and deleting rows in a named range alters the range for the named range. I intended doing this in the code but the named range is not getting updated with the new range when the insert or delete is done via a VBA. You need to understand that while I completely agree with the principle of copy/paste without selecting the ranges; in this case the selection is required because I don't know of any other way of identifying the range to update the named range. Amended code below. Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Application.EnableEvents = False 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if numnber rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged MsgBox "No rows inserted or deleted" 'Case 0 for testing purposes only and 'can be deleted when testing finished Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 .Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .ClearContents .Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data Sheets("Sheet1").Range("Sh1billsW").Copy Sheets("Sheet2").Select Sheets("Sheet2").Range("Sh2billsW").Select ActiveSheet.Paste ActiveWorkbook.Names.Add Name:="Sh2billsW", _ RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub -- Regards, OssieMac |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
I have been looking at this today. Obviously you will have problems trying to
insert rows where they interfer or break up merged cells and you will have to work around that and perhaps get rid of the merged cells. I have identified some other problems also. It is not inserting/deleting the rows in the right place. I will look into it further and let you know. (Might have to post a question about one of the issues myself.) -- Regards, OssieMac "ML0940" wrote: Ossie Here are my findings. First of all, you are doing a great job because I would not have gotten this far along without your help and it is working pretty darn good now. Ok, On worksheet 2, if there is a blank row just below the last row in the named range, then the macro works perfect. If there is data in the row directly below the last row in the named range, it is getting overwritten. Then explains the first problem, as I have a merged cell just below the range on Worksheet 2. So, it looks like a row is being added to the range, but not to worksheet 2. if we insert a row into the first range manually, of course it will work but we need to also insert that row in Worksheet 2. It isn't enough to just paste. To demonstrate what I am explaining, put some text into the cell directly below range 2, then in The code, add On Error Resume Next, above ActiveSheet.Paste You will then seehow range 2 is growing but no new cells are being added to the collection in Worksheet 2 Thank you Mark "OssieMac" wrote: My apologies. Further testing of the macro has shown up problems trying to copy and paste and/or trying to make a named range = another named range. Basically the second named range is not getting updated with the new range when rows are inserted/deleted. I cannot get the macro to work properly without select-copy- select destination-paste. The reason is that the pasted area becomes the new selection that can be renamed. In the interactive mode, inserting and deleting rows in a named range alters the range for the named range. I intended doing this in the code but the named range is not getting updated with the new range when the insert or delete is done via a VBA. You need to understand that while I completely agree with the principle of copy/paste without selecting the ranges; in this case the selection is required because I don't know of any other way of identifying the range to update the named range. Amended code below. Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Application.EnableEvents = False 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if numnber rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged MsgBox "No rows inserted or deleted" 'Case 0 for testing purposes only and 'can be deleted when testing finished Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 .Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .ClearContents .Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data Sheets("Sheet1").Range("Sh1billsW").Copy Sheets("Sheet2").Select Sheets("Sheet2").Range("Sh2billsW").Select ActiveSheet.Paste ActiveWorkbook.Names.Add Name:="Sh2billsW", _ RefersToR1C1:=Selection End If Application.EnableEvents = True End Sub -- Regards, OssieMac |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
OK. I have done some more work on this for you. The major bug that I had is
that when working with ranges assigned to variables there are problems associated with having these ranges in different worksheets when the code is in a specific sheet module. The work around is to put minimal code in the Sheet module and call the other code in a standard module. On the merged cells issue, the system will warn you if it tries to spit merged cells. This is a system message. You will have to work out how to get around that problem because you will already have inserted the rows in the first range and if you abort so as not to mess with the merged cells then you will end up with problems. You probably should avoid merged cells if you are going ahead with this method. Note that you will need to edit the sheet names that I have used and insert your sheet names. Insert the following code in the Sheet module where it will be executed by the worksheet change event:- Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Application.EnableEvents = False Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Call ProcessDataChange End If Application.EnableEvents = True End Sub Insert the following code in a standard module:- Sub ProcessDataChange() Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if number rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged MsgBox "No rows inserted or deleted" 'Case 0 for testing purposes only and 'can be deleted when testing finished Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .ClearContents Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data and Paste Special Values Sheets("Sheet1").Range("Sh1billsW").Copy Sheets("Sheet2").Range("Sh2billsW") _ .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub -- Regards, OssieMac |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi Ossie,
Again, thank you so much for your time. From the get go, I knew would not be an easy macro to create. If MS had insert events in their API, then perhaps it would be easier. Ossie, I did split the code like you explained, so, you were saying that part of the problem is that all of the code could not be in the change module? "On the merged cells issue, the system will warn you if it tries to spit merged cells. This is a system message. You will have to work out." Ossie, I think I see what the problem is, it is not the merged cell, it is that the way the code is programmed; it looks like we are shifting the range down by one cell, that is what is splitting up the merge, that is no good. If I insert a row in one range, then I need a row to be inserted in the second range. To just knock the range down by one cell, of course will cause problems with data below. I think that is what causes the problem. Also, on this try, I am getting an error now, every time I delete a row in rng 1 :( I guess there are reallly only 3 ways to go from here; 1. To say, we can't get it at this time but we have learned some valuable things from trying. 2. I can send you the spread sheet, so that you have the real spread sheet to try, so that you can see the problem, specifically. 3. May be let it go for now and if the answer comes, then we can re-visit it at that time. In the grand scheme of things, this macro is really awissh list item, not the most important item in the spread sheet. Please let me know, if you'd like to continue? If not, then I want to thank you so much for the help. I really appreciate it :) ML "OssieMac" wrote: OK. I have done some more work on this for you. The major bug that I had is that when working with ranges assigned to variables there are problems associated with having these ranges in different worksheets when the code is in a specific sheet module. The work around is to put minimal code in the Sheet module and call the other code in a standard module. On the merged cells issue, the system will warn you if it tries to spit merged cells. This is a system message. You will have to work out how to get around that problem because you will already have inserted the rows in the first range and if you abort so as not to mess with the merged cells then you will end up with problems. You probably should avoid merged cells if you are going ahead with this method. Note that you will need to edit the sheet names that I have used and insert your sheet names. Insert the following code in the Sheet module where it will be executed by the worksheet change event:- Private Sub Worksheet_Change(ByVal Target As Range) 'Exit Sub 'Use this to suppress macro 'if you want to fix corrupted data 'during the testing process or 'at any later date. Dim isect As Object 'Target Application.EnableEvents = False Set isect = Intersect(Target, Range("Sh1billsW")) If Not isect Is Nothing Then Call ProcessDataChange End If Application.EnableEvents = True End Sub Insert the following code in a standard module:- Sub ProcessDataChange() Dim rngSh1 As Range 'Range("Sh1billsW") Dim rngSh2 As Range 'Range("Sh2billsW") Dim lngCols As Long 'Number Columns in named ranges Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW Dim lngDiff As Long 'Difference in number of rows 'Assign each named range to VBA variables With Sheets("Sheet1") Set rngSh1 = .Range("Sh1billsW") End With With Sheets("Sheet2") Set rngSh2 = .Range("Sh2billsW") End With 'Assign number of rows in each range to VBA variables lngRowsSh1 = rngSh1.Rows.Count lngRowsSh2 = rngSh2.Rows.Count 'Assign number of columns in each range to VBA variable '(Assumes both ranges have same number of columns) lngCols = rngSh1.Columns.Count 'Assign difference in number or rows to VBA variable lngDiff = lngRowsSh1 - lngRowsSh2 'Test if number rows same, greater or less than Select Case lngDiff Case 0 'Number of rows unchanged MsgBox "No rows inserted or deleted" 'Case 0 for testing purposes only and 'can be deleted when testing finished Case Is 0 'INSERT rows in Range("Sh2billsW") With rngSh2 Range(.Cells(2, 1), _ .Cells(2 + lngDiff - 1, lngCols)) _ .Insert Shift:=xlDown End With Case Is < 0 'DELETE rows in Range("Sh2billsW") 'Note removal of negative from lngDiff with ABS function With rngSh2 .ClearContents Range(.Cells(2, 1), _ .Cells(2 + Abs(lngDiff) - 1, lngCols)) _ .Delete Shift:=xlUp End With End Select 'Copy data and Paste Special Values Sheets("Sheet1").Range("Sh1billsW").Copy Sheets("Sheet2").Range("Sh2billsW") _ .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub -- Regards, OssieMac |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Firstly I don't like giving up but I do understand that there is only so much
that can be done and sometimes it is not possible to achieve the desired results. However, I think that one of our problems is that I am not understanding what it is you want to achieve. Your quote:- it looks like we are shifting the range down by one cell My code is not shifting the range when a row is inserted, it is extending the range. For example:- I have named range Sh1billsW =Sheet1!$A$1:$C$77 and named range Sh2billsW =Sheet2!$E$11:$G$87 If I insert a row in Range Sh1billsW then its new range becomes Sheet1!$A$1:$C$78 and the code then inserts a row in Sh2billsW to make its new range =Sheet2!$E$11:$G$88 Your quote:- If I insert a row in one range, then I need a row to be inserted in the second range There are 2 ways of extending a named range. One is to simply extend the named range and the additional range at the bottom overwrites the cells below it. The other way is to insert the row so that it shifts the cells below. If you insert a row into the second named range then it is going to shift the cells below it. What am I not understanding correctly? How large is the excel file for emailing? I don't like the idea of posting my email address because of the spam but if you want to post yours then I will send an email to you to give you my email address. -- Regards, OssieMac |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Did you get the email I sent couple of days ago?
-- Regards, OssieMac "ML0940" wrote: Hi Ossie Sounds like a guy after my own heart :) I don't like to give up either but I do realize the frustrations that can come with trying to explain tedious things over a post; despite the fact I try to be as concise as I can be. Also, I help people with ACAD VBA problems every day and it does get to the point to where you do need to ask then to e-mail you or vice versa. This forum is very primitive in comparison to ACAD forums that I go to; in those, we can send private messages and there are also code wrappers. Also, the post replies come directly to your e-mail, so that you can try the code before coming back to the forum. I agree with you about the privacy, but I will give you my secondary e-mail address which is , I don't care about spam on that one. If you can send me an e-mail to there, I can then shoot you my primary one. Also, I could even invite you to my pc, if you'd like to work on it together, that would be great! Ossie, see where the problem is occuring but we I somehow am not explaining it well enough. You see, in the code you are doing, you are doing it precisely right, you are addressing the range in Range 2 in the event that range one expands or contracts. Now let's put the Range 2 aside for a moment. Now, lets' say on Range1, a row is inserted into the range, then Primarily, we need to increase Worksheet 2 by 1 row, that is the row collection. Then, secondary, Range 2 is adjusted. So, in the order of events 1. A row is inserted into Range 1, on sheet 1 2. On Sheet 2, a row is inserted directly "below" but not in Range2 3. Range 2 is increased by a cell, then pasted into place So, for every row that range 1 is increased, a row in Worksheet 2 is added, then Range 2 is increased. It is a 3 part solution. And vice versa: Does that make more sense? I think where we are mis communicating is that you are thinking that we just need to acct for the ranges, but we also need to acct for the rows in the worksheets as well. Hope to hear from you soon. ML "OssieMac" wrote: Firstly I don't like giving up but I do understand that there is only so much that can be done and sometimes it is not possible to achieve the desired results. However, I think that one of our problems is that I am not understanding what it is you want to achieve. Your quote:- it looks like we are shifting the range down by one cell My code is not shifting the range when a row is inserted, it is extending the range. For example:- I have named range Sh1billsW =Sheet1!$A$1:$C$77 and named range Sh2billsW =Sheet2!$E$11:$G$87 If I insert a row in Range Sh1billsW then its new range becomes Sheet1!$A$1:$C$78 and the code then inserts a row in Sh2billsW to make its new range =Sheet2!$E$11:$G$88 Your quote:- If I insert a row in one range, then I need a row to be inserted in the second range There are 2 ways of extending a named range. One is to simply extend the named range and the additional range at the bottom overwrites the cells below it. The other way is to insert the row so that it shifts the cells below. If you insert a row into the second named range then it is going to shift the cells below it. What am I not understanding correctly? How large is the excel file for emailing? I don't like the idea of posting my email address because of the spam but if you want to post yours then I will send an email to you to give you my email address. -- Regards, OssieMac |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Hi Ossie
Yes and I did reply to you yesterday evening You didn't get my e-mail? We are almost there; you are doing a very nice job. Hmmm. I typed a fairly sizable reply. I am going to e-mail you right now; please reply if you get it It will be from an MSN acct. Thank you! ML "OssieMac" wrote: Did you get the email I sent couple of days ago? -- Regards, OssieMac "ML0940" wrote: Hi Ossie Sounds like a guy after my own heart :) I don't like to give up either but I do realize the frustrations that can come with trying to explain tedious things over a post; despite the fact I try to be as concise as I can be. Also, I help people with ACAD VBA problems every day and it does get to the point to where you do need to ask then to e-mail you or vice versa. This forum is very primitive in comparison to ACAD forums that I go to; in those, we can send private messages and there are also code wrappers. Also, the post replies come directly to your e-mail, so that you can try the code before coming back to the forum. I agree with you about the privacy, but I will give you my secondary e-mail address which is , I don't care about spam on that one. If you can send me an e-mail to there, I can then shoot you my primary one. Also, I could even invite you to my pc, if you'd like to work on it together, that would be great! Ossie, see where the problem is occuring but we I somehow am not explaining it well enough. You see, in the code you are doing, you are doing it precisely right, you are addressing the range in Range 2 in the event that range one expands or contracts. Now let's put the Range 2 aside for a moment. Now, lets' say on Range1, a row is inserted into the range, then Primarily, we need to increase Worksheet 2 by 1 row, that is the row collection. Then, secondary, Range 2 is adjusted. So, in the order of events 1. A row is inserted into Range 1, on sheet 1 2. On Sheet 2, a row is inserted directly "below" but not in Range2 3. Range 2 is increased by a cell, then pasted into place So, for every row that range 1 is increased, a row in Worksheet 2 is added, then Range 2 is increased. It is a 3 part solution. And vice versa: Does that make more sense? I think where we are mis communicating is that you are thinking that we just need to acct for the ranges, but we also need to acct for the rows in the worksheets as well. Hope to hear from you soon. ML "OssieMac" wrote: Firstly I don't like giving up but I do understand that there is only so much that can be done and sometimes it is not possible to achieve the desired results. However, I think that one of our problems is that I am not understanding what it is you want to achieve. Your quote:- it looks like we are shifting the range down by one cell My code is not shifting the range when a row is inserted, it is extending the range. For example:- I have named range Sh1billsW =Sheet1!$A$1:$C$77 and named range Sh2billsW =Sheet2!$E$11:$G$87 If I insert a row in Range Sh1billsW then its new range becomes Sheet1!$A$1:$C$78 and the code then inserts a row in Sh2billsW to make its new range =Sheet2!$E$11:$G$88 Your quote:- If I insert a row in one range, then I need a row to be inserted in the second range There are 2 ways of extending a named range. One is to simply extend the named range and the additional range at the bottom overwrites the cells below it. The other way is to insert the row so that it shifts the cells below. If you insert a row into the second named range then it is going to shift the cells below it. What am I not understanding correctly? How large is the excel file for emailing? I don't like the idea of posting my email address because of the spam but if you want to post yours then I will send an email to you to give you my email address. -- Regards, OssieMac |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
If one range changes, update the other
Ossie
I see what I did DUHHH :) I replied back to my yahoo acct from my MSN acct. I sent them back to myy MSN acct. I will now forward them over to you I think it was a total of 3 Thank you ML "ML0940" wrote: Hi Ossie Yes and I did reply to you yesterday evening You didn't get my e-mail? We are almost there; you are doing a very nice job. Hmmm. I typed a fairly sizable reply. I am going to e-mail you right now; please reply if you get it It will be from an MSN acct. Thank you! ML "OssieMac" wrote: Did you get the email I sent couple of days ago? -- Regards, OssieMac "ML0940" wrote: Hi Ossie Sounds like a guy after my own heart :) I don't like to give up either but I do realize the frustrations that can come with trying to explain tedious things over a post; despite the fact I try to be as concise as I can be. Also, I help people with ACAD VBA problems every day and it does get to the point to where you do need to ask then to e-mail you or vice versa. This forum is very primitive in comparison to ACAD forums that I go to; in those, we can send private messages and there are also code wrappers. Also, the post replies come directly to your e-mail, so that you can try the code before coming back to the forum. I agree with you about the privacy, but I will give you my secondary e-mail address which is , I don't care about spam on that one. If you can send me an e-mail to there, I can then shoot you my primary one. Also, I could even invite you to my pc, if you'd like to work on it together, that would be great! Ossie, see where the problem is occuring but we I somehow am not explaining it well enough. You see, in the code you are doing, you are doing it precisely right, you are addressing the range in Range 2 in the event that range one expands or contracts. Now let's put the Range 2 aside for a moment. Now, lets' say on Range1, a row is inserted into the range, then Primarily, we need to increase Worksheet 2 by 1 row, that is the row collection. Then, secondary, Range 2 is adjusted. So, in the order of events 1. A row is inserted into Range 1, on sheet 1 2. On Sheet 2, a row is inserted directly "below" but not in Range2 3. Range 2 is increased by a cell, then pasted into place So, for every row that range 1 is increased, a row in Worksheet 2 is added, then Range 2 is increased. It is a 3 part solution. And vice versa: Does that make more sense? I think where we are mis communicating is that you are thinking that we just need to acct for the ranges, but we also need to acct for the rows in the worksheets as well. Hope to hear from you soon. ML "OssieMac" wrote: Firstly I don't like giving up but I do understand that there is only so much that can be done and sometimes it is not possible to achieve the desired results. However, I think that one of our problems is that I am not understanding what it is you want to achieve. Your quote:- it looks like we are shifting the range down by one cell My code is not shifting the range when a row is inserted, it is extending the range. For example:- I have named range Sh1billsW =Sheet1!$A$1:$C$77 and named range Sh2billsW =Sheet2!$E$11:$G$87 If I insert a row in Range Sh1billsW then its new range becomes Sheet1!$A$1:$C$78 and the code then inserts a row in Sh2billsW to make its new range =Sheet2!$E$11:$G$88 Your quote:- If I insert a row in one range, then I need a row to be inserted in the second range There are 2 ways of extending a named range. One is to simply extend the named range and the additional range at the bottom overwrites the cells below it. The other way is to insert the row so that it shifts the cells below. If you insert a row into the second named range then it is going to shift the cells below it. What am I not understanding correctly? How large is the excel file for emailing? I don't like the idea of posting my email address because of the spam but if you want to post yours then I will send an email to you to give you my email address. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update range in For loop | Excel Programming | |||
Sum range auto update | Excel Worksheet Functions | |||
Quickly Update Range | Excel Programming | |||
Update Range upon Load | Excel Programming | |||
How update range after web query? | Excel Programming |