vba Excel中的VBA代码将一行添加到多个工作表，然后从相邻行复制公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20251025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA code in Excel to add a row to multiple sheets and then copy formula from adjacent row
提问by Mark Hirst
I'm really hoping someone can help me with this one. I have recorded a macro to use within a sheet that needs to create a row at the same position on 2 worksheets and then, on one of them, copy the formula's in the cells from the row below it. The code I have looks like this -
我真的希望有人能帮助我解决这个问题。我已经录制了一个宏以在工作表中使用，该工作表需要在 2 个工作表的相同位置创建一行，然后在其中一个工作表上从其下方的行复制单元格中的公式。我的代码看起来像这样 -
Sub Macro1()
Sheets(Array("SCHEDULE", "ANNUAL SUMMARY")).Select
Sheets("SCHEDULE").Activate
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("ANNUAL SUMMARY").Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.AutoFill Destination:=ActiveCell.Offset(-1, 0).Rows("1:2").EntireRow _
, Type:=xlFillDefault
ActiveCell.Offset(-1, 0).Rows("1:2").EntireRow.Select
Sheets("SCHEDULE").Select
ActiveCell.Select
My problem is, when I run it manually and then record the macro, it does exactly what I want it to, but when I run this from a button on the "SCHEDULE" sheet it does not copy the formula's from the row below the one on the "ANNUAL SUMMARY" sheet.
我的问题是，当我手动运行它然后记录宏时，它完全符合我的要求，但是当我从“SCHEDULE”表上的按钮运行它时，它不会从下面的行复制公式在“年度摘要”表上。
Can anyone help to get this working with me?
任何人都可以帮助我解决这个问题吗？
Thanks all in advance
提前谢谢大家
Mark
标记
采纳答案by Sam
The problem with the macro recorder is that although it can give you a good indication of what code you need, it also generates very inefficient code and includes all of the select and activate statements that you need to try and avoid using.
宏记录器的问题在于，虽然它可以很好地指示您需要什么代码，但它也会生成非常低效的代码，并且包含您需要尝试避免使用的所有 select 和 activate 语句。
Any reference in the code to ActiveCellis referring to the cell that is currently selected and ActiveSheetis the sheet that is currently selected. This can give you undesired results if you run the macro from a different sheet that the macro was recorded from...
代码中的任何引用ActiveCell都指的是当前选定的单元格，并且是当前选定ActiveSheet的工作表。如果您从记录宏的不同工作表运行宏，这可能会给您带来不希望的结果...
If you wanted to copy row 1 from SCHEDULE sheet then you can use
如果您想从 SCHEDULE 表中复制第 1 行，则可以使用
Sheets("SCHEDULE").Rows(1).Copy Sheets("ANNUAL SUMMARY").Rows(1)
If you want to auto fill a range, then this can be accomplished with a single line of code
如果你想自动填充一个范围，那么这可以用一行代码来完成
This will auto fill the contents of row1 (column A - E) down to row 100 in your ANNUAL SUMMARY sheet
这将自动填充第 1 行（A - E 列）的内容，直到您的年度总结表中的第 100 行
Sheets("ANNUAL SUMMARY").Range("A1:E100").FillDown
So if we put it all together and include some declarations for our source and destination sheet to make the sub more readable..
因此，如果我们将它们放在一起并为我们的源表和目标表包含一些声明，以使 sub 更具可读性..
Sub CopyAndFillDownExample()
Dim rowNumber As Long, offset As Long
Dim sourceSht As Worksheet, destinationSht As Worksheet
'set the source and destinationsheets
Set sourceSht = Sheets("SCHEDULE")
Set destinationSht = Sheets("ANNUAL SUMMARY")
'number of rows to copy down
offset = 100
'get currently selected row
rowNumber = ActiveCell.Row
'copy the selected row from the source sheet to the destination sheet
sourceSht.Rows(rowNumber).Copy destinationSht.Rows(rowNumber)
'fill down the formulas
destinationSht.Rows(rowNumber & ":" & rowNumber + offset).FillDown
End Sub
