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
_ _
^¢¯^
--

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

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

--
Regards,
Tom Ogilvy




"Diana" wrote in message
...
Excel workbook; how do I insert row in all worksheets?

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
"Diana" wrote:

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

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

On Sun, 5 Sep 2004 10:59:06 -0700, "Diana"
wrote:

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

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.

--
Regards,
Tom Ogilvy

"sebastienm" wrote in message
...
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
"Diana" wrote:

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

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

Regards,
Sébastien

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"sebastienm" wrote in message
...
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
"Diana" wrote:

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

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

--
Regards,
Tom Ogilvy

"sebastienm" wrote in message
...
I only have xl2000 and the code works perfectly fine on my machine.

Regards,
Sébastien

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"sebastienm" wrote in message
...
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
"Diana" wrote:

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

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

Tom Ogilvy wrote:

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

"sebastienm" wrote in message

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

Regards,
Sébastien

"Tom Ogilvy" wrote:


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.

--
Regards,
Tom Ogilvy

"sebastienm" wrote in message
...

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
"Diana" wrote:


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

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.

--
regards,
Tom Ogilvy


"Sandy V" wrote in message
...
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

Tom Ogilvy wrote:

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

"sebastienm" wrote in message

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

Regards,
Sébastien

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

"Tom Ogilvy" wrote:

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.

--
regards,
Tom Ogilvy


"Sandy V" wrote in message
...
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

Tom Ogilvy wrote:

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

"sebastienm" wrote in message

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

Regards,
Sébastien