Private Sub Worksheet_Change(ByVal Target As Range)
Dim iShe As String, iRow&, iSheet As Worksheet, str_Bm As String
Dim iSh&
If Target.Count = 1 And Target.Row > 2 Then
Application.EnableEvents = False
If Target.Column = 3 Then
iRow = Target.Row
If Target.Value = "" Then GoTo 10
Range("B" & iRow) = iRow - 2
str_Bm = Target.Offset(0, -1).Value & Target.Value
For Each iSheet In Worksheets
If iSheet.Name = str_Bm Then MsgBox "已有该工作表建立啦!": GoTo 10
Next iSheet
Sheets("模板").Range("K3").Value = Format(Sheets("模板").Range("K3") + 1, "00")
iSh = Sheets.Count
Worksheets("模板").Copy after:=Worksheets(iSh)
With Sheets("模板 (2)")
.Visible = -1
.Range("H3") = Target.Value
.Name = str_Bm
.Select
.Range("A5").Select
.Range("A5:I5").Font.Name = "Times New Roman"
.Range("A5:I5").Font.Size = 14
GoTo 10
End With
End If
If Target.Column = 4 Then
str_Bm = Target.Offset(0, -2).Value & Target.Offset(0, -1)
For Each iSheet In Worksheets
If iSheet.Name = str_Bm Then iSheet.Range("B3") = Target.Value
Next
End If
End If
10 Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iShe As String, iRow&, iSheet As Worksheet
If Target.Row > 2 And Target.Count = 1 And Target.Column = 3 Then
iRow = Target.Row
If Target.Value = "" Then Exit Sub
iShe = Range("B" & iRow) & Range("C" & iRow)
For Each iSheet In Worksheets
If iSheet.Name = iShe Then
Sheets(iShe).Visible = -1
Sheets(iShe).Select
Exit Sub
End If
Next iSheet
End If
End Sub
Sub AAA()
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim iRow&, str_Pm As String, str_Gg As String, iRng As Range
Dim x&, y&, i As Double
If Sh.Name = "目录" Then Exit Sub
If Sh.Name = "模板" Then Exit Sub
If Target.Row > 4 And Target.Count = 1 Then
iRow = Target.Row + 1
If Target.Column = 1 Then
Cells(iRow, 1).Resize(1, 2).Merge
Cells(iRow, 3).Resize(1, 2).Merge
Cells(iRow, 5).Resize(1, 2).Merge
Cells(iRow, 7).Resize(1, 2).Merge
Cells(iRow, 1).Resize(1, 9).Borders.LineStyle = 1
Cells(iRow, 1).Resize(1, 8).HorizontalAlignment = xlCenter
Cells(iRow, 1).Resize(1, 8).VerticalAlignment = xlCenter
Cells(iRow, 9).HorizontalAlignment = xlLeft
Target.Offset(0, 2).Select
ElseIf Target.Column = 3 Or Target.Column = 5 Then
Cells(iRow - 1, 7) = "=Sum(G" & iRow - 2 & ",C" & iRow - 1 & ") - E" & iRow - 1
Target.Offset(0, 2).Select
str_Pm = Range("H3").Value
str_Gg = Range("B3").Value
i = Cells(iRow - 1, 7).Value
With Sheets("目录")
For x = 3 To .Range("C65536").End(3).Row
If .Cells(x, 3) = str_Pm And .Cells(x, 4) = str_Gg Then
.Cells(x, 5) = i
Exit Sub
End If
Next x
End With
End If
End If
End Sub
</p>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iShe As String, iRow&, iSheet As Worksheet, str_Bm As String
Dim iSh&
If Target.Count = 1 And Target.Row > 2 Then
Application.EnableEvents = False
If Target.Column = 3 Then
iRow = Target.Row
If Target.Value = "" Then GoTo 10
Range("B" & iRow) = iRow - 2
str_Bm = Target.Offset(0, -1).Value & Target.Value
For Each iSheet In Worksheets
If iSheet.Name = str_Bm Then MsgBox "已有该工作表建立啦!": GoTo 10
Next iSheet
Sheets("模板").Range("K3").Value = Format(Sheets("模板").Range("K3") + 1, "00")
iSh = Sheets.Count
Worksheets("模板").Copy after:=Worksheets(iSh)
With Sheets("模板 (2)")
.Visible = -1
.Range("H3") = Target.Value
.Name = str_Bm
.Select
.Range("A5").Select
.Range("A5:I5").Font.Name = "Times New Roman"
.Range("A5:I5").Font.Size = 14
GoTo 10
End With
End If
If Target.Column = 4 Then
str_Bm = Target.Offset(0, -2).Value & Target.Offset(0, -1)
For Each iSheet In Worksheets
If iSheet.Name = str_Bm Then iSheet.Range("B3") = Target.Value
Next
End If
End If
10 Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iShe As String, iRow&, iSheet As Worksheet
If Target.Row > 2 And Target.Count = 1 And Target.Column = 3 Then
iRow = Target.Row
If Target.Value = "" Then Exit Sub
iShe = Range("B" & iRow) & Range("C" & iRow)
For Each iSheet In Worksheets
If iSheet.Name = iShe Then
Sheets(iShe).Visible = -1
Sheets(iShe).Select
Exit Sub
End If
Next iSheet
End If
End Sub
Sub AAA()
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim iRow&, str_Pm As String, str_Gg As String, iRng As Range
Dim x&, y&, i As Double
If Sh.Name = "目录" Then Exit Sub
If Sh.Name = "模板" Then Exit Sub
If Target.Row > 4 And Target.Count = 1 Then
iRow = Target.Row + 1
If Target.Column = 1 Then
Cells(iRow, 1).Resize(1, 2).Merge
Cells(iRow, 3).Resize(1, 2).Merge
Cells(iRow, 5).Resize(1, 2).Merge
Cells(iRow, 7).Resize(1, 2).Merge
Cells(iRow, 1).Resize(1, 9).Borders.LineStyle = 1
Cells(iRow, 1).Resize(1, 8).HorizontalAlignment = xlCenter
Cells(iRow, 1).Resize(1, 8).VerticalAlignment = xlCenter
Cells(iRow, 9).HorizontalAlignment = xlLeft
Target.Offset(0, 2).Select
ElseIf Target.Column = 3 Or Target.Column = 5 Then
Cells(iRow - 1, 7) = "=Sum(G" & iRow - 2 & ",C" & iRow - 1 & ") - E" & iRow - 1
Target.Offset(0, 2).Select
str_Pm = Range("H3").Value
str_Gg = Range("B3").Value
i = Cells(iRow - 1, 7).Value
With Sheets("目录")
For x = 3 To .Range("C65536").End(3).Row
If .Cells(x, 3) = str_Pm And .Cells(x, 4) = str_Gg Then
.Cells(x, 5) = i
Exit Sub
End If
Next x
End With
End If
End If
End Sub
</p>