Change activeworksheet with index

[nando88]

I currently have 14 worksheets and the first 2 have data pulled from the internet.
What I would like to do is when the data is updated, I will place data in different sheets that will be switched dynamically with index in a for loop.
I already have some code, but it is not working.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'.Display StatusBar = False
.EnableEvents = False
.DisplayAlerts = False
End With

Set ws1 = Sheets("Data")
If Not Intersect(Target, ws1.Range("A2:CA1048576")) Is Nothing Then
'Clear contents in Report

lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
For m = 1 To 12
k = 2
n = 2
    For i = 2 To lr
        j = i + 2
        If Not IsEmpty(ws1.Cells(i, (55 + m))) And ws1.Cells(i, (55 + m)).Value <> "" Then
            If m = 1 Then
                Sheet3.Activate
            ElseIf m = 2 Then
                Sheet4.Activate
            ElseIf m = 3 Then
                Sheet5.Activate
            ElseIf m = 4 Then
                Sheet6.Activate
            ElseIf m = 5 Then
                Sheet7.Activate
            ElseIf m = 6 Then
                Sheet8.Activate
            ElseIf m = 7 Then
                Sheet9.Activate
            ElseIf m = 8 Then
                Sheet10.Activate
            ElseIf m = 9 Then
                Sheet11.Activate
            ElseIf m = 10 Then
                Sheet12.Activate
            ElseIf m = 11 Then
                Sheet13.Activate
            ElseIf m = 12 Then
                Sheet14.Activate
            End If
            ActiveSheet.Range("A2:L1048576").Clear
            ActiveSheet.Cells(k, 1) = ws1.Cells(i, (1))
            ActiveSheet.Cells(k, 2) = ws1.Cells(i, (19 + m))
            ActiveSheet.Cells(k, 3) = ws1.Cells(i, (31 + m))
            ActiveSheet.Cells(k, 4) = ws1.Cells(i, (43 + m))
            ActiveSheet.Cells(k, 5) = ws1.Cells(i, (55 + m))
            k = k + 1
        End If
        If i = lr Then
            ActiveSheet.Cells((i + 2), 1) = ActiveSheet.Range(ws3.Cells(3, m), ActiveSheet.Cells(1048576, m)).Cells.SpecialCells(xlCellTypeConstants).Count
            ActiveSheet.Cells((i + 3), 4) = Application.WorksheetFunction.Sum("D3:D" & lr)
            ActiveSheet.Cells((i + 4), 5) = Application.WorksheetFunction.Sum("E3:E" & lr)
         
            Sheets(m).Cells((i + 2), 1).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
            Sheets(m).Cells((i + 3), 4).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
            Sheets(m).Cells((i + 4), 5).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
        End If
    Next i
    Next m
End If

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
'.Display StatusBar = True
.EnableEvents = True
.DisplayAlerts = True
End With

End Sub

 

[GlennUK]

In what way is it not working?