Hi Ossie
Yes and I did reply to you yesterday evening
You didn't get my e-mail?

We are almost there; you are doing a very nice job.

Hmmm. I typed a fairly sizable reply.

I am going to e-mail you right now; please reply if you get it
It will be from an MSN acct.

Thank you!
ML

"OssieMac" wrote:

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

--
Regards,

OssieMac


"ML0940" wrote:

Hi Ossie
Sounds like a guy after my own heart :)
I don't like to give up either but I do realize the frustrations that can
come with trying to explain tedious things over a post; despite the fact I
try to be as concise as I can be.

Also, I help people with ACAD VBA problems every day and it does get to the
point to where you do need to ask then to e-mail you or vice versa.

This forum is very primitive in comparison to ACAD forums that I go to; in
those, we can send private messages and there are also code wrappers.
Also, the post replies come directly to your e-mail, so that you can try the
code before coming back to the forum.

I agree with you about the privacy, but I will give you my secondary e-mail
address which is , I don't care about spam on that one.
If you can send me an e-mail to there, I can then shoot you my primary one.

Also, I could even invite you to my pc, if you'd like to work on it
together, that would be great!

Ossie, see where the problem is occuring but we I somehow am not explaining
it well enough. You see, in the code you are doing, you are doing it
precisely right, you are addressing the range in Range 2 in the event that
range one expands or contracts.

Now let's put the Range 2 aside for a moment.

Now, lets' say on Range1, a row is inserted into the range, then Primarily,
we need to increase Worksheet 2 by 1 row, that is the row collection.
Then, secondary, Range 2 is adjusted.

So, in the order of events
1. A row is inserted into Range 1, on sheet 1
2. On Sheet 2, a row is inserted directly "below" but not in Range2
3. Range 2 is increased by a cell, then pasted into place
So, for every row that range 1 is increased, a row in Worksheet 2 is added,
then Range 2 is increased.
It is a 3 part solution.

And vice versa:

Does that make more sense?

I think where we are mis communicating is that you are thinking that we just
need to acct for the ranges, but we also need to acct for the rows in the
worksheets as well.


Hope to hear from you soon.

ML


"OssieMac" wrote:

Firstly I don't like giving up but I do understand that there is only so much
that can be done and sometimes it is not possible to achieve the desired
results.

However, I think that one of our problems is that I am not understanding
what it is you want to achieve.

Your quote:-
it looks like we are shifting the range down by one cell

My code is not shifting the range when a row is inserted, it is extending
the range. For example:-

I have named range Sh1billsW =Sheet1!$A$1:$C$77 and
named range Sh2billsW =Sheet2!$E$11:$G$87

If I insert a row in Range Sh1billsW then its new range becomes
Sheet1!$A$1:$C$78 and
the code then inserts a row in Sh2billsW to make its new range
=Sheet2!$E$11:$G$88

Your quote:-
If I insert a row in one range, then I need a row to be inserted in the
second range

There are 2 ways of extending a named range. One is to simply extend the
named range and the additional range at the bottom overwrites the cells below
it. The other way is to insert the row so that it shifts the cells below. If
you insert a row into the second named range then it is going to shift the
cells below it.

What am I not understanding correctly?

How large is the excel file for emailing? I don't like the idea of posting
my email address because of the spam but if you want to post yours then I
will send an email to you to give you my email address.

--
Regards,

OssieMac