Enter Row Number as a value in cells

Hi I'm using tables in excel and importing data from different documents. I have got the browse, open, and import (copy paste) nailed down.

when I import the data into the table it populates all but the first column as this is a unique sequential ID. I has chosen to use the row number.

So just before import in the vba I sort the table via this ID column and add the new data to the end.

I then want to add the unique sequential ID number to the new data.

so far I have.... nothing that works

    Dim lastRow As Long

    ' get the last row from column A that has a value
    Dim FirstBlankCell As Range
    Set FirstBlankCell = Workbooks("Temp").Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

    lastRow = Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row

    ' use the last row to determine how far down to extend the formula
    'Worksheets("Sheet1").Range("A" & FirstBlankCell).Formula = "=row()"
    'Worksheets("Sheet1").Range("D" & lastRow + 1).Value = Workbooks(Filename).Name
        
'lrowp = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'Worksheets("Sheet1").Range("A2:A" & lrowp).Copy
'Worksheets("Sheet1").Range("A2:A" & lrowp) _
'        .PasteSpecial Paste:=xlPasteValues

MsgBox FirstBlankCell

any ideas? When I have this working I also want to include the file name in the last column as when opening the file the first check is has it already been imported

thanks

additional information - I have the copy paste as values only as when sorting tables with a formula reference row it obviously changes to what ever row it sits in messing up the data

How does the code not work?

If you don't want the formula do a copy and paste special values.

With Worksheets("Sheet1").Range("Table1[Item number]").SpecialCells _
     (xlCellTypeBlanks)
     .Formula = "=row()-1"
     .Value = .Value
End With