Clearing an error when adding Rows to multiple Sheets

Question

I have asked this before but can't seem to figure out how to get the code I'm using to appear cleanly in a comment so people can read it. So reposting here.

Question: I want to set up a button that will allow a user to add a row to the active sheet they are working on. In the background I want the spreadsheet to add a row in another sheet in the same workbook in the same location as the one that was added. (the two sheets are identical only with the non active worksheet having info from the previous month) The reason I need it to do this is because the Active worksheet is pulling data from the inactive sheet and I want to keep the same number of rows in both sheets with the data on both in the same rows so it will pull the correct data. Thank you for any advice you have!

I added copying the formulas from the selection row as well so the user does not need to copy manually and I think that is what is causing the issue.

Any idea on how to clear the error:

Run-time error '1004': PasteSpecial method of Range class failed

Got the idea for the code I used at MS Excel VBA how to insert a row in the current worksheet and three others

My code

Sub AddRow() ' ' Row Macro

ActiveWorkbook.Names.Add Name:="CopyRow", RefersToR1C1:=Rows(ActiveCell.Row)

Range("CopyRow").Select

Selection.Copy

Dim Lst As Long

Lst = ActiveCell.Row

Worksheets("SOV Detailed Breakdown").Rows(Lst).Insert Worksheets("Previous Application").Rows(Lst).Insert

    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False


ActiveWorkbook.Names("CopyRow").Delete

End Sub

This does everything I want it to but with errors. Any thoughts? — user2993438

David Zemens David Zemens · Accepted Answer · 2013-11-14T22:39:37

The error raises on the PasteSpecial method, which isn't necessary. When the clipboard is filled like this, the next Insert statement automatically does the paste operation.

I tidy it up to get rid of the Name assigment which I don't think you really need.

Sub AddRow() ' ' Row Macro
Dim Lst As Long

Rows(ActiveCell.Row).Copy

Lst = ActiveCell.Row

Worksheets("SOV Detailed Breakdown").Rows(Lst).Insert 
Worksheets("Previous Application").Rows(Lst).Insert

End Sub

Clearing an error when adding Rows to multiple Sheets

1 Answers