Automatically number newly added Rows (Insert Shift:=xlDown)

[TomCooper88]

Hello People!

I hope someone here can help me... I have made a macro that copies a row into a new sheet:

Sheets("Datasheet").Select
Range("A2").Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("MailMerge").Select
Range("A7:G7").Select
Selection.Copy
Sheets("Datasheet").Select
Range("A2:G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

What I want now, is that everytime a new row is copied in the second row (first row are names),
The cell A2 to get a number that is +1 to the last one. So starting with 1, the second copied row would be 2, etc etc...

I hope there's somebody out there who knows the answer!

Kind Regards,
Tom

 

[ukmikeb]

Hi

Try :-

Dim TempA2 As Long
Sheets("Datasheet").Select
Range("A2").Select
TempA2 =Range("A2").Value

ActiveCell.EntireRow.Insert Shift:=xlDown

Range("MailMerge!A7:G7").Copy


Range("A2:G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Value = TempA2 + 1

hth

 

[TomCooper88]

It worked! Thanks a million!!
Tom

 

[ukmikeb]

Hi Tom

Thanks for the feedback.

You could reduce your code to the following by removing the "Selects" :-

Range("Datasheet!A2").EntireRow.Insert Shift:=xlDown

Range("MailMerge!A7:G7").Copy

Range("Datasheet!A2:G2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("Datasheet!A2").Value = Range("Datasheet!A3").Value  + 1

Pleased to have helped solve your problem.

Good luck with your project.