|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 junjei521 于 2016-11-21 14:37 编辑
求助在根据B列的合计字段自动插入行,因为有公式的单元格列被保护,不想显示单元格,想在插入行前用代码先解锁,解锁密码为 12345,并进行以下代码的复制过程后,继续保护带有公式的单元格,保护密码为123456
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng, a, b, c As Range
a = Target.Row()
b = Target.Column() - 1
Set Rng = Columns("b:b").Find(what:="合计")
If Target.Column <> 7 Or Target.Count > 1 Or Target.Row < 3 Or Target.Row > Rng.Row Then
Exit Sub
ElseIf Target.Row - Rng.Row = -1 Then
' And Cells(a, b + 1) = ""
Rows(Rng.Row).Insert
a = Target.Row()
b = Target.Column() - 1
Range(Cells(a - 1, 2), Cells(a - 1, 37)).Copy 'Sheets("记录").Range("b4:e13").Clear
Range(Cells(a, 2), Cells(a, 37)).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Range(Cells(a - 1, 2), Cells(a - 1, 2)).Copy 带公式的单元格列
Range(Cells(a, 2), Cells(a, 2)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(a - 1, 9), Cells(a - 1, 9)).Copy 带公式的单元格列
Range(Cells(a, 9), Cells(a, 9)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(a - 1, 14), Cells(a - 1, 14)).Copy 带公式的单元格列
Range(Cells(a, 14), Cells(a, 14)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(a - 1, 36), Cells(a - 1, 36)).Copy 带公式的单元格列
Range(Cells(a, 36), Cells(a, 36)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells(a, 1).Select
ElseIf Rng.Row - Target.Row > 1 And Cells(a, b + 1) = "" Then
Rows(Rng.Row - 1).Delete
End If
' End If
'End If
End Sub
以下代码:
|
|