If one range changes, update the other

[ML0940]

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

 

[OssieMac]

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

 

[ML0940]

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]

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.

 

[ML0940]

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

 

[ML0940]

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

 

[OssieMac]

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

 

[OssieMac]

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

 

[ML0940]

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?

 

[ML0940]

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]

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]

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.)

 

[OssieMac]

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

 

[ML0940]

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]

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.

 

[ML0940]

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 (e-mail address removed), 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]

Did you get the email I sent couple of days ago?

 

[ML0940]

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

 

[ML0940]

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