问使用KeyCells从不同的工作表创建工作表更改

EN

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range, ColNum As Long, TotalCol As Long, LeftFixedCol As Long
Dim Rng As Range, c As Range

Set KeyCells = Range("B2")
 
If Application.Intersect(KeyCells, Target) Is Nothing Then Exit Sub
If IsNumeric(KeyCells.Value) = False Then Exit Sub
 

ColNum = KeyCells.Value
If ColNum <= 0 Then Exit Sub
Set Rng = Range(Cells(4, 1), Cells(4, Columns.Count))
Set c = Rng.Find("END")     
If c Is Nothing Then Exit Sub
TotalCol = c.Column
LeftFixedCol = 1 


Dim i As Integer
If TotalCol < LeftFixedCol + ColNum + 1 Then 
        For i = TotalCol To LeftFixedCol + ColNum
        Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Cells(4, i).Value = "Member" & i - LeftFixedCol 
        Cells(5, i).Value = "=DATA!$A$2"
        Cells(6, i).Value = "=OFFSET(DATA!$C$2,COLUMN()-2,0)"
        Cells(7, i).Value = "=OFFSET(DATA!$D$2,COLUMN()-2,0)"
        Cells(8, i).Value = "=OFFSET(DATA!$E$2,COLUMN()-2,0)"
        Cells(10, i).Value = "=OFFSET(DATA!$F$2,COLUMN()-2,0)"
        Cells(12, i).Value = "=OFFSET(DATA!$G$2,COLUMN()-2,0)"
        Cells(13, i).Value = "=OFFSET(DATA!$H$2,COLUMN()-2,0)"
        Cells(14, i).Value = "=OFFSET(DATA!$I$2,COLUMN()-2,0)"
        Next i
End If
If TotalCol > LeftFixedCol + ColNum + 1 Then 
        For i = TotalCol - 1 To LeftFixedCol + ColNum + 1 Step -1
            Columns(i).Delete
        Next i

End If
End Sub