|This is probably a simple fix, but I'm tired of fooling with it... |
Setup: Ad hoc "database" with primary key in Column Q
Row 1 contains header information, and it is "split" horizontally
Cell Q2 contains a simple =(MAX(Q4:Q1000))+1 formula
Row 3 is a blank row, unformatted
The purpose of the macro is to click/select where a new row needs to go in, then click a button to run the macro - inserting a row, copying the contents of Q2 [my max+1 counter] into my new row, in the 17th column [Q column].
This all worked fine if I manually removed the split, then ran the macro. I decided to jazz it up, adding a step to both remove the split, then re-add it after the counter was placed.
The macro works perfectly the FIRST time. The second time, it adds a new Row 1 and copies the contents of the new Q2 [formerly Q1, before the row addition] and adds the split back. If I rem out the split addition, it continues to work.
FYI, I'm clicking on an entirely new row prior to running the macro the second time, so "activecell" should be the new row I've selected prior to the second macro run, correct?
In summary - What can I do to make it acknowledge my second new row position and/or not think that Row 1 is the place where I want my new row inserted?
Application.ScreenUpdating = False
.SplitRow = 0
Dim Rng, n As Long, k As Long
Rng = 1
If Rng = "" Then Exit Sub
k = ActiveCell.Offset(0, 0).Row
n = Cells(k, 17).End(xlToLeft).Column
Range(ActiveCell, ActiveCell.Offset(0, 0)).Select
Range(ActiveCell, ActiveCell.Offset(0, 0)).EntireRow.Insert
Range(Cells(k, 17), Cells(k + Val(Rng) - 1, 17)).Formula = Range("Q2").Value
.SplitRow = 1
Application.ScreenUpdating = True