I am trying to create an Excel file which will be updated up on opening using the workbook open event. I make one cell a counter, which will count starting 1 when the file is opened first, then 2 when the file is opened second time and so on. I want to copy the value of the counter to the cells in row 3 starting from column H, that is, H1 = 1, I1 =2 etc. The problem is whenever I open the first time, 1 is copied to all the cells. Please see the screenshot below:

Here is my code:

Private Sub Workbook_Open()

Dim i As Integer, lastcol As Long, lastrow As Long, j As Integer

lastrow = Cells(Rows.Count, 2).End(xlUp).Row
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column

Cells(100, 1).Value = Cells(100, 1).Value + 1


For i = 2 To lastrow
    lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
    For j = 8 To 10
    Cells(2, j).Value = Cells(100, 1).Value
    If Cells(2, j + 1).Value = Cells(2, j).Value Then
    Exit For
    End If
    Next j
       
Next i

End Sub