Copy/Paste and AutoNumbering

[FalconFlyer]

I am copy some data from one worksheet into a master worksheet (same workbook). The master worksheet has a unique ID (next number). After I copy the data over the master worksheet, I would like to fill in the ID. What I need the macro to do is fill in the missing ID numbers to the end of the data. My ID is in Column A. The end of the data should be based off of Column B. For example, I need to fill in ID 1003 - 1005 for the corresponding data in Column B. The code I have been trying is below.

Column A Column B
1001 abc
1002 def
ghi
jkl
mno

Sub AutoNum()

Dim LastRow As Long
Dim FirstRow As Long
Dim Start As Long
Dim NextNumber As Long

LastRow = Range("B" & Rows.Count).End(xlUp).Row
FirstRow = Range("A" & Rows.Count).End(xlUp).Row + 1
NextNumber = WorksheetFunction.Max(Sheet1.Range("A:A")) + 1

    For counter = FirstRow To LastRow
    Range("A" & counter).Value = NextNumber
Next counter
End Sub

With this code, I keep getting "1" as the next ID number. I do not understand what I am doing wrong. I have tried the MAX function by itself and it is still giving me "1", but when I write it in Excel as =MAX(A:A) +1, I am getting the expected answer.

Thank you in advance.

 

[Dr. Demento]

This works for me:

Replace:

Range("A" & counter).Value = NextNumber

With:

      If Range("A" & counter).Value = vbNullString Then _
        Range("A" & counter).Value = Range("A" & counter - 1).Value + 1

This test to see that the AutoNumber is blank and if it is, fill the blank with the value above plus one. If there are gaps (you've got three blank spaces (1003-1005) but the next autonumber should be 1004), it's obviously going to fail.

hth

 

[FalconFlyer]

Thank you very much. That seems to work. I really appreciate your (speedy) help!

 

[Dr. Demento]

You're welcome. Thanks for the feedback and welcome to the board!!