Insert Row also adds formulas and moves cursor

  • Thread starter Thread starter night_writer
  • Start date Start date
N

night_writer

I cannot explain this behavior, which is occurring when I do the action from a macro or manually:

I have a number of cells adjacent to each other, some of which are in a named range and some are not.

My code is as follows:

Sub Renum_AddRow()

Sheets("ReNum").Select
Call UnprotectOne
Range("ReNumTemplate").Select
Selection.EntireRow.Insert
Range("ReNumTemplate").Copy Destination:=ActiveCell

Call ProtectOne

End Sub

ReNumTemplate = b29:h29.
Some of the cells include formulas that refer to Sheet 'C-Locs&AIs'.

When I insert the row, new formulas appear in the cells, but they are not just the formulas above or below with the relative references changed. For example, cell B29's formula is: ='C-Locs&AIs'!I31 & ", " & 'C-Locs&AIs'!J31 & ", " & 'C-Locs&AIs'!K31. The formula which appears after I add the row is: ='C-Locs&AIs'!D31. In another cell, the formula I expect should reference column B, but instead references column E.

And the cursor moves itself to F28, which is a few cells to the right in the newly added row.

All of this from a single command to "Insert Row." this happens regardless of whether I am using a macro, or doing the insert manually.

I have closed and reopened the spreadsheet, and even shut down my computer completely, but no joy. Is there anyone who can explain this? could this single sheet be corrected?

Would appreciate any thoughts or comments.
 
hi,

before copy, check that ActiveCell is part of Column B

If ActiveCell.Column = 2 Then

isabelle


Le 2013-08-12 18:23, (e-mail address removed) a écrit :
 
also you can move ActiveCell on the same row in column B with the
following code

Application.Goto Cells(ActiveCell.Row, 2)

isabelle

Le 2013-08-14 22:34, isabelle a écrit :
 
Back
Top