Formatting Access Export to Excel

[derekben]

Hi,


I am looking to export a table to excel from access. I would like to add a column with a header of RefNumber and it will add one every row until the last row of data, starting at 1 (1,2,3,4 ect.) I have successfully added the title and have been able to find how to calculate getting the last row but I can not get it to go down the column and add one. This is the code I have so far. ANY HELP WOULD BE AMAZING! THANK YOU.

NOTE: oWB.Sheets(1) is referring to the export excel document being created by this button.

oWB.Sheets(1).Range("G1").Value = "RefNumber"


Dim x As Integer
Dim total As Integer
total = 1
x = oWB.Sheets(1).UsedRange.Rows.Count


oWB.Sheets(1).Range("G2").Value = total


For i = 1 To x
oWB.Sheets(1).Range("G3").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = total
oWB.Sheets(1).Range("G3").Value = total


total = total + 1
Next

 

[Alansidman]

Assuming you have values in Column A and using that as your determinent for the last row, this should work for you

Sub Insert()
Dim x As Integer
Dim i As Integer
Dim total As Integer
    x = Range("A" & Rows.Count).End(xlUp).Row

    total = 1
    i = i + 1
Sheets(1).Range("G3").Value = total

For i = 3 To x
Sheets(1).Cells(i, 7).Select
ActiveCell.Value = total
total = total + 1
Next i
End Sub