Inserting a row in one named range and having it update another

M

ML0940

Hi
I am trying to insert a row into a named range and have it update the size
of another range so that they are both identical and make the data in each
range the same.

I think I am almost there but not quite.
Also the below code is a little more then needed but I was using it for some
tests.
Can anyone help me get to that result?

Also, I would love this to happen on a change event, "if" the named range is
changed

Any help is appreciated

Thanks!
Mark

Sub UpdateRange()

Dim Sh1bW As Range
Dim RowNums As Integer
Dim CurrRow As Long

Set Sh1bW = Range("Sh1billsW")

'If a row is added or deleted to/from the range, return the range row count
If Sh1bW.Rows.Count + 1 Then
RowNums = Sh1bW.Rows.Count
'MsgBox "Number of Rows = " & RowNums
Sh1bW.Rows.Select
End If

'Return the row and value of each cell in the range
For Each Cell In Sh1bW
CurrRow = Cell.Row
'MsgBox "Row " & CurrRow & " = " & vbCrLf & Cell.Value
'Debug.Print "Row " & CurrRow & " = " & vbCrLf & Cell.Value
Next Cell
MsgBox RowNums

Dim Sh1bM As Range
Set Sh1bM = Range("Sh1billsM")

Sh1bM.Rows.Resize = RowNums

For Each Cell In Sh1bW
Sh1bM.Cells.Value = Sh1bW.Cells.Value
Sh1bM.Rows.Select
Next Cell

MsgBox Sh1bM.Rows.Count

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top