Active Row Query

[daz_uk]

Hi,

I am trying to write a Macro which inserts a row below the Active Cell and then copys a pre-defined row (which contains formula's) and then pastes it into the new row.

The problem I have is that once I move away from the newly inserted row (to copy the row containing the formua's) how do I get excel to go back to the newly inserted row, as it no longer has a point of reference.

Hope someone can help

Thanks

[JeanRage]

Hi,

Without looking at your code, it is a bit difficult to give you a precise answer ...

But in generic terms, you always store in a variable of yours a given row number for example ...

HTH

[nchavan]

Hi daz_uk,

Try below code:

Please Login or Register  to view this content.

[daz_uk]

Hi Nchavan,

Thanks for the code.

I have added your code to my code, but have 1 final question.

Dim i As Integer, j As Integer
'i contains the row number of the activecell
ThisWorkbook.Sheets(1).Activate
i = ActiveCell.Row
'i = ThisWorkbook.Sheets(1).ActiveCell.Row
ThisWorkbook.Sheets(1).Cells(i + 1, 1).EntireRow.Insert shift:=xlDown
'j contains the row number of the newly inserted row
j = i + 1
'Do your stuff here now as you have j holding the reference for the newly added row after the activecell
ActiveCell.Offset(1).EntireRow.Insert
Range("A1:AN1").Select
Selection.Copy
Selection j
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

I know the syntax is wrong (I have highlighted it in bold) but how do reselect "j" in order to do my paste?

Thanks

[nchavan]

Hi daz_uk,

As the variable j is holding the row number of newly added row you have to use it as below:

Please Login or Register  to view this content.

[romperstomper]

Just FYI, you can simplify your code to:

Please Login or Register  to view this content.

[daz_uk]

wow, thanks everyone for the help