Inserting New Row (in the Middle) of Two Worksheets

rjbinney

Active Member
Joined
Dec 20, 2010
Messages
250
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this

Code:
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

Active Member
Joined
Dec 20, 2010
Messages
250
Office Version
  1. 365
Platform
  1. Windows
Mind. BLOWN.

Explain how that worked??!?!?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Active Member
Joined
Dec 20, 2010
Messages
250
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,010
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top