MS Excel VBA 如何在当前工作表和其他三个工作表中插入一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17715102/
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
MS Excel VBA how to insert a row in the current worksheet and three others
提问by Glyn
I am using MS Excel 2007 and Microsoft Visula Basic 6.5.
我使用的是 MS Excel 2007 和 Microsoft Visula Basic 6.5。
I want to select a row in a sheet and then use a macro to insert a row in this sheet and three others. I am trying the code below however when I run it I get the error "Run-time error '1004': Application-defined or object-defined error" on "Worksheets("Rel. Planning Meeting").Range(Lst).Activate".
我想在工作表中选择一行，然后使用宏在此工作表和其他三个工作表中插入一行。我正在尝试下面的代码，但是当我运行它时，我在“工作表（“Rel。规划会议”）上收到错误“运行时错误'1004'：应用程序定义或对象定义的错误。范围（Lst）。启用”。
Sub Copy1()
Dim Lst As Long
'save the rowNo
Lst = ActiveCell.Row
Worksheets("Rel. Planning Meeting").Activate
Worksheets("Rel. Planning Meeting").Range(Lst).Activate
ActiveCell.EntireRow.Insert
Worksheets("Master Release Plan - HTSTG").Activate
Worksheets("Master Release Plan - HTSTG").Range(Lst).Activate
ActiveCell.EntireRow.Insert
Worksheets("Inst. Gateway").Activate
Worksheets("Inst. Gateway").Range(Lst).Activate
ActiveCell.EntireRow.Insert
Worksheets("CAB").Activate
Worksheets("CAB").Range(Lst).Activate
ActiveCell.EntireRow.Insert
Worksheets("Rel. Planning Meeting").Range("A3:G5000").Copy _
Destination:=Worksheets("Master Release Plan - HTSTG").Range("A3")
Worksheets("Master Release Plan - HTSTG").Range("A3:O5000").Copy _
Destination:=Worksheets("Inst. Gateway").Range("A3")
Worksheets("Inst. Gateway").Range("A3:T5000").Copy _
Destination:=Worksheets("CAB").Range("A3")
End Sub
I have tried "Dim Lst As Range" however then I get the error "Run time error '91': Object variable or With block variable not set" on "Lst = ActiveCell.Row".
我已经尝试过“将 Lst 作为范围”但是然后我在“Lst = ActiveCell.Row”上收到错误“运行时错误'91'：对象变量或未设置块变量”。
Your help is greatly appreciated.
非常感谢您的帮助。
Regards,
问候，
Glyn
格林
New Code:
新代码：
Sub InsertRow()
Dim Lst As Long
'save the rowNo
Lst = ActiveCell.Row
'Insert a row in each worksheet at the currently selected cell.
Worksheets("Rel. Planning Meeting").Rows(Lst).Insert
Worksheets("Master Release Plan - HTSTG").Unprotect
Worksheets("Master Release Plan - HTSTG").Rows(Lst).Insert
Worksheets("Inst. Gateway").Unprotect
Worksheets("Inst. Gateway").Rows(Lst).Insert
Worksheets("CAB").Unprotect
Worksheets("CAB").Rows(Lst).Insert
End Sub
回答by chris neilsen
In addition to @Bharath 's answer it is not necassary and a generally bad idea to use Select.
Your row insertion code can be reduced to
除了@Bharath 的回答之外，使用Select.
您的行插入代码可以减少到
Worksheets("Rel. Planning Meeting").Rows(Lst).Insert
Worksheets("Master Release Plan - HTSTG").Rows(Lst).Insert
Worksheets("Inst. Gateway").Rows(Lst).Insert
Worksheets("CAB").Rows(Lst).Insert
回答by Bharath Raja
in the 2nd line of your code, you are assigning lst, a value(activecell.row), which is a long variable. if you write
在代码的第 2 行中，您正在为 lst 分配一个值（activecell.row），它是一个长变量。如果你写
Worksheets("Rel. Planning Meeting").Range(Lst).Activate
that means ex - Worksheets("Rel. Planning Meeting").Range(4).Activate, if ur activecell.row is 4. This will not work on a range.
这意味着前 - Worksheets("Rel. Planning Meeting").Range(4).Activate，如果你的 activecell.row 是 4。这不会在一个范围内工作。
If you want to select a row then change your existing line and all other similar lines, where you have range(lst) to -
如果要选择一行，请更改现有行和所有其他类似行，其中 range(lst) 为 -
Worksheets("Rel. Planning Meeting").Rows(Lst).Activate
I would suggest not selecting anything on the sheet as far as possible because this may slow up the execution time. Your's is a very simple macro here but it is good practice to avoid selecting or activating stuff on your sheet as much as possible.
我建议尽可能不要在工作表上选择任何内容，因为这可能会减慢执行时间。您的宏在这里是一个非常简单的宏，但最好避免选择或激活工作表上的内容。
Hope this helps.
希望这可以帮助。
