|
以下我本人录制的一段宏,要达到的目的是插入行,同时复制上面行的公式,现只能实现的每点击一次“插入行”(指定了宏按钮)可以插入固定的行数(5行),但当要插入的行数较多时,就比较麻烦,现希望能做到每点击“插入行”就出现一个对话框,提示插入多少行,然后输入要插入的行数,这样可以一步到位,想请高手帮忙,并还请高手能不能使代码尽量简化,先谢了!
Sub Macro3()
'
' Macro3 Macro
' 宏由 yangkaifu 录制,时间: 2011-12-31
'
'
ActiveSheet.Unprotect
Rows("7:11").Select
Selection.Insert Shift:=xlDown
Range("B6:AD6").Select
Selection.AutoFill Destination:=Range("B6:AD11"), Type:=xlFillDefault
Range("B6:AD11").Select
Range("F13").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub
ykfexcel 发表于 2012-1-2 20:15
感谢sunjing-zxl ,学习了,不过我还希望不需要提示输入行号,就直接从7行(或某个其他固定行)开始插入并复 ... - Sub 插入复制()
- Dim Ro As Long, n As Long
- Ro = 7
- ActiveSheet.Unprotect
- n = InputBox("输入行数", "请输入需要插入行的行数")
- Rows(Ro & ":" & Ro + n - 1).Insert
- Rows(Ro - 1).Copy Rows(Ro & ":" & Ro + n - 1)
- ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
- False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
- AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
- :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
- AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
- AllowUsingPivotTables:=True
- End Sub
复制代码
|
|