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

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

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

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

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

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

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

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

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

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

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

Via code, making 2 named Ranges Indentical, upon change
 

Peter,
Close, 2 seperat names by indentical info, tthat is row count and info.
The named ranges oonly have text, no formulas or aything else
So, if I change one, then the other is changed to match.
Think of them like indentical twins but with different names

Thank you
Mark

"Peter T" wrote:

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

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

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

Via code, making 2 named Ranges Indentical, upon change
 

Ossie, thank you very much!
In the case of this code, I ddid not need to use ofsett, I don't know what I
thinking, that is likely because I was using it in a macro earlier in the day
:)
From looking at your code, it does look like this may do the trick.
I did not think that I had turned the events completely off; I have never
disable events before but that is great that you can do that.
Yes, I thought that the paste special-paste values was much better, I think
you agree. This way, what ever your cells are currently formatted to; they
will keep that format, as we are only pasting in values and not formatting as
with paste.
Therefore, the need to format cells after the fact is erased.
OK, let me go try and report back.
Mark

"OssieMac" wrote:

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

Via code, making 2 named Ranges Indentical, upon change
 

Hey Ossie I think you are getting there but still, not quite there yet.
I just played with the code for a good 30 minutes, here are the results.

Ok,
The first thing is that The Sheet Number (in this method) needs to look like
this Sheets(2) otherwise it will give me a range out of subscript error, but
that was an easy fix.

If I use paste special, I get an error that says:
The information can not be pasted because the copy area and the paste area
are not the same size and shape.

Therefore, it looks like it saying that both named ranges have to be
indentical; I can not see that being necessary. I have used paste values
100's of times and never got that error, but that is what it is saying.

Then, if I use paste, I get an error:
Can not change part of a merged cell

That draws attention to another problem; that is, if the row size is +1,
then we need an xldown, and -1, an xlup oor something to that effect.
I do not think a straight copy and paste will work because if the frist
range is increased by a cell, then pasted to another area, it could overlap
with other cells; as is the case here. So, even more dynamic thinking is in
order, I think.

Still, it is a great try, we jst need to keep plugging.
Also, if we could say the values in range 2 = range 1, then we can eliminate
the need for a copy/paste, I think.

ML


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(2).Range("Sh2billsW").ClearContents

Range("Sh1billsW").Copy

'Need to select sheet before you can
'select a range on the sheet
Sheets(2).Select

ActiveSheet.Range("Sh2billsW").Select
' Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Paste 'alternative paste

'Rename the currently selected area (range)
ActiveWorkbook.Names.Add Name:="Sh2billsW", _
RefersToR1C1:=Selection

Sheets(1).Select
Application.CutCopyMode = False

End If

Application.EnableEvents = True

End Sub


"OssieMac" wrote:

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

Via code, making 2 named Ranges Indentical, upon change
 

Ossie
Check this out
I had some time to play around today.
Ok, so I could see exactly what was going on,
I placed the 2 named ranges on the same worksheet.

Now, I was able make your code wor without any need for a copy or paste.
I had not yet tried it on the real workbook yet but
Check this out:

Private Sub Worksheet_Change(ByVal Target As Range)
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

'Ossie, it was this simple:
'I think, now the ranges will dynamically change on each worksheet as well
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

End Sub

Mark





"OssieMac" wrote:

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

Via code, making 2 named Ranges Indentical, upon change
 

Very cool!
So, now, after you insert a row in the first range, then type something in
that blank cell and the second range will dynamically pick it up.
I like it!

ML

"ML0940" wrote:

Ossie
Check this out
I had some time to play around today.
Ok, so I could see exactly what was going on,
I placed the 2 named ranges on the same worksheet.

Now, I was able make your code wor without any need for a copy or paste.
I had not yet tried it on the real workbook yet but
Check this out:

Private Sub Worksheet_Change(ByVal Target As Range)
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

'Ossie, it was this simple:
'I think, now the ranges will dynamically change on each worksheet as well
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

End Sub

Mark





"OssieMac" wrote:

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