Hi Ossie
I really do appreciate the code and examples
I will copy them now and give them a try.
I have ben also helping fellow AutoCAD users with VBA today as well.
My whole life is VBA LOL
No, it isn't, but a lot of it is :)

From looking at the code, it is interesting to see how you are handling it
but I need to run it to see tht I fully understand what is going on.

Ossie, I don't have any problem with the pasting, as long as pasting doesn't
affect anything else like in the original posts. If the paste works, then I
will switch itto paste special by values again; assuming that works.
The reason I like the paste special by values is because on different
worksheets I may have different size fonts etc; so if I just do paste, then
it carries over the format as well, whereas paste special by values will just
carry over the values and not the formatting.

Ok, let me go try and get back to you.

Thank you again!

ML

PS: Why don't they just have insert events? :)



"OssieMac" wrote:

My apologies. Further testing of the macro has shown up problems trying to
copy and paste and/or trying to make a named range = another named range.

Basically the second named range is not getting updated with the new range
when rows are inserted/deleted.

I cannot get the macro to work properly without select-copy-
select destination-paste.

The reason is that the pasted area becomes the new selection that can be
renamed.

In the interactive mode, inserting and deleting rows in a named range alters
the range for the named range. I intended doing this in the code but the
named range is not getting updated with the new range when the insert or
delete is done via a VBA.

You need to understand that while I completely agree with the principle of
copy/paste without selecting the ranges; in this case the selection is
required because I don't know of any other way of identifying the range to
update the named range.

Amended code below.

Private Sub Worksheet_Change(ByVal Target As Range)

'Exit Sub 'Use this to suppress macro
'if you want to fix corrupted data
'during the testing process or
'at any later date.

Dim isect As Object 'Target
Dim rngSh1 As Range 'Range("Sh1billsW")
Dim rngSh2 As Range 'Range("Sh2billsW")
Dim lngCols As Long 'Number Columns in named ranges
Dim lngRowsSh1 As Long 'Number Rows in Range Sh1billsW
Dim lngRowsSh2 As Long 'Number Rows in Range Sh2billsW
Dim lngDiff As Long 'Difference in number of rows

Set isect = Intersect(Target, Range("Sh1billsW"))

If Not isect Is Nothing Then

Application.EnableEvents = False

'Assign each named range to VBA variables
With Sheets("Sheet1")
Set rngSh1 = .Range("Sh1billsW")
End With

With Sheets("Sheet2")
Set rngSh2 = .Range("Sh2billsW")
End With

'Assign number of rows in each range to VBA variables
lngRowsSh1 = rngSh1.Rows.Count
lngRowsSh2 = rngSh2.Rows.Count

'Assign number of columns in each range to VBA variable
'(Assumes both ranges have same number of columns)
lngCols = rngSh1.Columns.Count

'Assign difference in number or rows to VBA variable
lngDiff = lngRowsSh1 - lngRowsSh2

'Test if numnber rows same, greater or less than
Select Case lngDiff
Case 0 'Number of rows unchanged
MsgBox "No rows inserted or deleted"
'Case 0 for testing purposes only and
'can be deleted when testing finished
Case Is 0
'INSERT rows in Range("Sh2billsW")
With rngSh2
.Range(.Cells(2, 1), _
.Cells(2 + lngDiff - 1, lngCols)) _
.Insert Shift:=xlDown
End With

Case Is < 0
'DELETE rows in Range("Sh2billsW")
'Note removal of negative from lngDiff with ABS function
With rngSh2
.ClearContents
.Range(.Cells(2, 1), _
.Cells(2 + Abs(lngDiff) - 1, lngCols)) _
.Delete Shift:=xlUp
End With
End Select

'Copy data
Sheets("Sheet1").Range("Sh1billsW").Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("Sh2billsW").Select
ActiveSheet.Paste
ActiveWorkbook.Names.Add Name:="Sh2billsW", _
RefersToR1C1:=Selection

End If

Application.EnableEvents = True

End Sub


--
Regards,

OssieMac