Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
Dim i As Integer

Do While i < 100
i = i + 1
   Worksheets("Sheet1").Range("B2").End(xlDonw).Select
   Worksheets("Sheet1").Range("A2").Cells(i, 1).Value = i

   Next
   Worksheets("Sheet2").Range("B2").End(xlDonw).Select
   Worksheets("Sheet2").Range("A2").Cells(i, 1).Value = i
   
   Next
   Worksheets("Sheet3").Range("B2").End(xlDonw).Select
   Worksheets("Sheet3").Range("A2").Cells(i, 1).Value = i
   
   Next
   Worksheets("Sheet4").Range("B2").End(xlDonw).Select
   Worksheets("Sheet4").Range("A2").Cells(i, 1).Value = i
   
   Next
   Worksheets("Sheet5").Range("B2").End(xlDonw).Select
   Worksheets("Sheet5").Range("A2").Cells(i, 1).Value = i

Loop
End Sub

Code: Select all

Dim sh As Worksheet
Dim rall As Range, r As Range, i As Integer
For Each sh In Worksheets
    i = 0
    Set rall = sh.Range("b2", sh.Range("b" & sh.Rows.Count).End(xlUp))
    For Each r In rall
        i = i + 1
        r.Offset(0, -1).Value = i
    Next r
Next sh