How do you insert a row into all worksheets?

[lbass]

I found you can group all the sheets using
sheets.select
But even with all the sheets grouped
inserting a row happens only with the last active sheet.

How can I insert a row in all the worksheets?

Thanks

Yes I tried searching - guess I just didn't know what to search for.

 

[just_jon]

untested --

sheets.select
rows("1:1").select
with selection
.insert shift:=xldown
end with

 

[Smitty]

How about something like this:

Sub InsertRows()
    Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            ws.Activate
            Range("A1").EntireRow.Insert
        Next ws
End Sub

Hope that helps,

Smitty

 

[digglez]

select all the sheet tabs for those you want to insert into,
select the row number you are inserting,

right click, insert.

Digglez

 

[plettieri]

Hi:

The procedure the OP works on my 2002 version....what version are you using?

plettieri

 

[lbass]

Thanks - I haven't checked Smitty's yet - all the rest work.

How do I get it to do it just above the cursor, where ever it may be?

 

[Smitty]

How do I get it to do it just above the cursor, where ever it may be?

How's this:

Sub InsertRows()
    Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            ws.Activate
            If ActiveCell.Row <> 1 Then
                ActiveCell.Offset(-1, 0).EntireRow.Insert
                Else: ActiveCell.EntireRow.Insert
            End If
        Next ws
End Sub

Hope that helps,

Smitty

 

[lbass]

Supper!!!!

When I checked out your first reply it worked fine just inserted above A1.

Thanks

 

[lbass]

Need just a little more help.
say there are 10 sheets A1:a44 have 1-44 in them
first sheet active cell is A1 = 1
Second sheet is A2 = 2
and so on
in sheet 10 (active sheet) active cell is a10 = 10 and want to insert row so that A10 is blank and A11 = 10
And want this to happen in all the sheets

I can get it to happen to only sheet 10 or I can get
A1 blank and A2 = 1 in all the sheets

I can not figure how to lock sheet 10 A10 as the active cell and
have A10 become the active cell in all the sheets.

Thanks for your help

 

[just_jon]

the below doesn't work?
Sub foo()
Sheets.Select
Rows("10:10").Select
With Selection
.Insert shift:=xlDown
End With
End Sub