Excel Workbook

[Soo Cheon Jheong]

Diana,

- - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub Test()

Dim SHT As Worksheet

For Each SHT In Worksheets
SHT.Rows(1).Insert
Next

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Guest]

Excel workbook; how do I insert row in all worksheets?

 

[Tom Ogilvy]

Sub AABB()
Worksheets.Select
Range("B7").EntireRow.Select
Selection.Insert
ActiveSheet.Select
End Sub

 

[Guest]

Hi Diana,
You can shelect multiple sheets by pressing the shift or ctrl key while
selecting them , then all actions done to the active sheet is automatically
done to other selected sheets. Click a non-selected sheet or the active sheet
tab to ungroup them.
Programmatically: the bellow example selects/groups sheets sheet1 and
sheet2, and insert arow at 16.
Sheets(Array("Sheet1", "Sheet2")).Select
Rows("16:16").Insert Shift:=xlDown

Regards,
Sebastien

 

[Gord Dibben]

Diana

Right-click on any sheet tab and "select all sheets".

Insert your row on active sheet and will be done to all sheets.

DO NOT FORGET to ungroup the sheets when task completed.

What you do to one sheet in a group is done to all.

Gord Dibben Excel MVP

 

[Tom Ogilvy]

What version of Excel? That doesn't work in Excel 97 or Excel 2000 (as
expected). VBA doesn't provide much support for grouped sheets. If you use
the selection object, you can get some of that functionality.

 

[Guest]

I only have xl2000 and the code works perfectly fine on my machine.

Regards,
Sébastien

 

[Tom Ogilvy]

think you need to look closer at the non active sheet. Sure you aren't
assuming the row was added there?

 

[Sandy V]

To confirm what Tom said, Sébastien's code does not work for me on
non-active albeit selected/grouped sheet(s) in my xl97 and xl2k.

However this from the macro recorder does:

Sheets(Array("Sheet1", "Sheet2")).Select
'Sheets("Sheet1").Activate 'recorded but not necessary
Rows("16:16").Select
Selection.Insert Shift:=xlDown

But Sébastien's "manual" tip works fine.

Regards,
Sandy

think you need to look closer at the non active sheet. Sure you aren't
assuming the row was added there?

If you

use

 

[Tom Ogilvy]

Sandy V,
Thanks for the confirmation.

As I said, using the selection object can get some things to work with
grouped sheets. The code I originally posted is similar to what you
recorded:

Sub AABB()
Worksheets.Select
Range("B7").EntireRow.Select
Selection.Insert
ActiveSheet.Select
End Sub

( the OP did say all sheets).

I had no contention with the manual methods Sébastien described - hope my
post was clear on that.

 

[Guest]

Yes, you're right Tom. I had first recorded and checked the same code as
Sandy, but stupidly, i played a bit too much with the code before posting it
in its original form.

Regards,
Sébastien