Macro for pasting a sequence of numbers to many rows in one column

[FreshDK]

Hi,

I have a sheet with 10.000 rows of data:

aaa	111
aaa	222
bbb	111
ccc	111
ccc	222
ccc	333
...	...

Sometimes I need to filter and sort the rows to view only certain rows in a specific order. But I need index numbers to be able to get back to the original order, so i do like this:

1	aaa	111
2	aaa	222
3	bbb	111
4	ccc	111
5	ccc	222
6	ccc	333
...10.000	...	...

My problem is when I add new rows to the sheet, it is never in the bottom, but always at a specific place. Then I mess up the index numbers because they need to be "dead" and not generated by a formula not to mess up when i do filter and sorting:

1	aaa	111
2	aaa	222
3	bbb	111
	bbb	222
4	ccc	111
5	ccc	222
6	ccc	333
...10.000	...	...

Right now I have this neat macro to add line below (with formulas) when i double click inside my sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
    Cancel = True
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

My wish is to add to this macro so that it "updates" my index numbers (pastes a new sequence of numbers from A1 to A100000) after inserting a new row. Right now it's very fragile if i don't remember to update index numbers before the occasional filter and sorting, then i cannot go back without messing up my dataset.

I have tried to google, but I cannot seem to find what I'm looking for.

Br.

FreshDK

 

[FreshDK]

I found a workaround macro to copy a range from a different sheet. It works but it is not elegant!