Inserting New Row (in the Middle) of Two Worksheets

[rjbinney]

This seems really simple.

When I "record" the Macro, it works. When I repeat, or try anything with my (limited) VBA skills, it does not.

I have two worksheets.

I need a macro where the user clicks on a cell in Sheet A, and Excel adds a new row immediately below that row in Sheet A AND Sheet B.

So if the user selects SheetA!G12 and runs the macro, a row is inserted between SheetA!Row 12 and 13, and ALSO SheetB!Row 12 and 13; if the user selects SheetA!K47 and runs the macro, a row is inserted between SheetA!Row 47 and 48, and ALSO between SheetB!Row 47 and 48, and so on.

I've tried:
Sheets("A").Activate
Sheets(Array("A", "B")).Select
ActiveCell.Offset(1, 0).EntireRow.Select
Selection.EntireRow.Insert


Sheets("A").Activate
Sheets(Array("A", "B")).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.EntireRow.Insert


Sheets(Array("A", "B")).Select
Sheets("A").Activate
ActiveSheet.Cells(ActiveCell.Row, 1).Select
Selection.Offset(1, 0).Select
Application.CutCopyMode = False
Selection.EntireRow.Insert



When I run any of these, it selects both sheets, but only adds the new row to Sheet A, not Sheet B.

It's staring me in the face, but I'm stuck.

 

[VoG]

Try like this

Sub test()
Dim r As Long, ws As Worksheet
r = ActiveCell.Row + 1
For Each ws In Sheets(Array("Sheet1", "Sheet2"))
    ws.Rows(r).Insert
Next ws
End Sub

 

[rjbinney]

Mind. BLOWN.

Explain how that worked??!?!?

 

[VoG]

Not exactly rocket science. The variable r stores the selected row on the active sheet then uses that to insert the correct row on each sheet specified in the array.

 

[rjbinney]

Oh Shoot Now What.

So here's the next challenge.

I need to go to Sheet 1 and copy and paste certain formulas from the row above to the new row. Cool. I can do that.

Then I need to go to Sheet 2 and copy and paste certain formulas from the row above (but from different columns) to the new row.

Trouble is, the user can leave any cell selected on Sheet 2 before going over to Sheet 1 and inserting the row.

How do I make the "new" row active?

I don't want to just search for the first/last blank row, as there may be other reasons why a user may have left a row blank. I need to activate THAT row.

Argh.