Wednesday 31 May 2017

MS Excel VBA how to insert a row in the current worksheet and three others



I am using MS Excel 2007 and 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".




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



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


Answer



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


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



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.



Hope this helps.


No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...