Serial number in all sheets excel macros

Sub Serial_number()
On Error Resume Next

Dim tot_Sheets As Integer
tot_Sheets = Application.Sheets.Count
Dim s As Integer
For s = 1 To tot_Sheets
'Sheets(ActiveSheet.Index + 1).Activate
If s <> 0 Then Sheets(s).Activate
'----------------serial number on sheets----------------
'Check last row_number for a filled cell in sheets
Dim colName As String
colName = "A"
Dim LastCell As Integer
LastCell = Sheets(s).Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row
'insert a new column to assign serial number
Sheets(s).Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove
'----------------------
'assigning serial number
Dim j As Integer
Dim i As Integer
j = LastCell
For i = 1 To j
Sheets(s).Range("A" & i + 1).Value = i
Next i
Dim c As Integer
Sheets(s).Range("A" & i).Delete
'-----------------------
Next s
End Sub

Add a consecutive serial number into multiple sheets in a single excel file

The below code will help you to create a breaking serial numbers into multiple sheets in an excel macros. Suppose we have a number of four sheets into an excel workbook and each sheets containg few records. The scenario from below code will assign an unique numbers to all four sheets where the number is ending to the last record.

On above scenario sheet1 is having 6 records and assgnig with 1-6 serial number. Similarly for all sheet2,sheet3,sheet4 creating an unique number where the number is ending into the last sheet.

Note : This code is valid upto 4 sheets.

Sub AddSerialNumbers()
On Error GoTo Last
Dim colName As String
'----------------serial numb1r on sheet1----------------
'Check last row_number for a filled cell in sheet1
colName = "A"
Dim LastCell As Integer
LastCell = Sheet1.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

'insert a new column to assign serial number
Sheet1.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j As Integer
Dim i As Integer
j = LastCell
For i = 1 To j
Sheet1.Range("A" & i + 1).Value = i
Next i
Dim c As Integer
Sheet1.Range("A" & i).Delete
'----------------serial number on sheet2----------------
'Check last row_number for a filled cell in sheet2
Dim LastCell2 As Integer
LastCell2 = Sheet2.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

'insert a new column to assign serial number
Sheet2.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j1 As Integer
Dim i1 As Integer
j1 = LastCell2
For i1 = 1 To j1
Sheet2.Range("A" & i1 + 1).Value = i - 2 + i1
Next i1
Dim t As Integer
t = i - 2 + i1
Sheet2.Range("A" & i1).Delete
'----------------serial number on sheet3----------------
'Check last row_number for a filled cell in sheet3
Dim LastCell3 As Integer
LastCell3 = Sheet3.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

'insert a new column to assign serial number
Sheet3.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j2 As Integer
Dim i2 As Integer
j2 = LastCell3
For i2 = 1 To j2
Sheet3.Range("A" & i2 + 1).Value = t - 2 + i2
Next i2
Dim t1 As Integer
t1 = i2 - 2 + t
Sheet3.Range("A" & i2).Delete
'----------------serial number on sheet4----------------
'Check last row_number for a filled cell in sheet4
Dim LastCell4 As Integer
LastCell4 = Sheet4.Range(colName & ":" & colName).SpecialCells(xlCellTypeLastCell).Row

 'insert a new column to assign serial number
Sheet4.Columns("A:A").Insert Shift:=xlToRight, _
CopyOrigin:=xlFormatFromLeftOrAbove

'assigning serial number
Dim j3 As Integer
Dim i3 As Integer
j3 = LastCell4
For i3 = 1 To j3
Sheet4.Range("A" & i3 + 1).Value = t1 - 2 + i3
Next i3
Sheet4.Range("A" & i3).Delete
Last:
End Sub