MS Excel VBA如何在当前工作表和其他三个工作表中插入一行 [英] MS Excel VBA how to insert a row in the current worksheet and three others

问题描述

我正在使用MS Excel 2007和Microsoft Visula Basic 6.5.

I am using MS Excel 2007 and Microsoft Visula Basic 6.5.

我想在工作表中选择一行，然后使用宏在该工作表中插入一行，然后在其他三个表中插入一行.我正在尝试下面的代码，但是当我运行它时，在"Worksheets("Rel.Planning Meeting").Range(Lst)上收到错误运行时错误'1004':应用程序定义的错误或对象定义的错误".激活".

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".

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

我尝试了按范围将Dim Lst设置为Dim"，但是随后在"Lst = ActiveCell.Row"上收到了错误运行时错误'91':对象变量或未设置块变量".

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".

非常感谢您的帮助.

此致

格林

新代码:

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

推荐答案

在代码的第二行中，您正在分配lst值(activecell.row)，该值是一个长变量.如果您写

in the 2nd line of your code, you are assigning lst, a value(activecell.row), which is a long variable. if you write

Worksheets("Rel. Planning Meeting").Range(Lst).Activate

表示ex-Worksheets("Rel.Planning Meeting").Range(4).如果ur activecell.row为4，则激活.此操作不适用于范围.

that means ex - Worksheets("Rel. Planning Meeting").Range(4).Activate, if ur activecell.row is 4. This will not work on a range.

如果要选择一行，请更改现有行以及所有其他类似的行，其中您的range(lst)为-

If you want to select a row then change your existing line and all other similar lines, where you have range(lst) to -

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.

希望这会有所帮助.

这篇关于MS Excel VBA如何在当前工作表和其他三个工作表中插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！