If the selected cell was the one to start with and the activesheet was one of
those Eng, Scot, Wales, I think I'd use something like:

Option Explicit
Sub testme01()
Dim wks As Worksheet
dim myAddr as string
myAddr = activecell.address

For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot", "Wales"))
With wks
.Select
.range(myaddr).entirerow.insert
.Range("Ad5:AE5").AutoFill _
Destination:=.Range("AD5:AE593"), Type:=xlFillDefault
.Range("D5").Select
End With
Next wks
End Sub

I still don't like working with selection.

George Gee wrote:

Ok, I see your point about the the position of the cursor.

So if the code grouped the worksheets, and the cursor was in the
correct cell, on the 'first' sheet ("Eng") ....

Sheets(Array("Eng", "Scot", "Wales")).Select
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot", "Wales"))
With wks
.Select
Selection.EntireRow.Insert
.Range("Ad5:AE5").AutoFill _
Destination:=.Range("AD5:AE593"), Type:=xlFillDefault
.Range("D5").Select
End With
Next wks
End Sub

This seems to work as I want it to.

Many thanks Dave for showing me the way, and for getting
me to think a little for myself!

George

Dave Peterson wrote:
It would scare me to leave it up to a cell I have selected on a
worksheet that isn't active--I just don't keep track of where I leave
the cursor when I change sheets.

But...

Option Explicit
Sub testme01()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot",
"Wales")) With wks
.Select
ActiveCell.EntireRow.Insert '-- changed
.Range("Ad5:AE5").AutoFill _
Destination:=.Range("AD5:AE593"), Type:=xlFillDefault
.Range("D5").Select
End With
Next wks
End Sub

George Gee wrote:

Thanks Dave for your response.

You code works fine!
However, I do not know, and the end user does not know,
where in the worksheet the new row(s) will be inserted! (At this
moment).

Can your code be altered to insert a new row, at (or just above) the
selected cell, on all worksheets?

Again, many thanks for your assistance with this.

George

Dave Peterson wrote:
There are somethings that work with grouped sheets when you do them
manually. But won't work when you do them via code.

Selection.entirerow.insert
worked fine when I did it manually, but didn't work via code.

The autofill worked find manually and via code.

I think I'd dump the grouped sheets and just process each sheet
separately.

This kind of thing should work:

Option Explicit
Sub testme01()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot",
"Wales")) With wks
.Select
.Range("a999").EntireRow.Insert
.Range("Ad5:AE5").AutoFill _
Destination:=.Range("AD5:AE593"),
Type:=xlFillDefault .Range("D5").Select
End With
Next wks
End Sub

But since your selection.entirerow.insert relied on some other
code--or the current selection, I couldn't tell what was going on.
I just used A999--correct it to what you want.

Ps. Very rarely do you have to select anything to work on it. But
you do have to select the worksheet before you select a range (D5)
on that worksheet.



George Gee wrote:

B. R.Ramachandran

Many thanks for your response, however ....
I do know how to do all that you are suggesting.

I am trying to make a worksheet noddy-proof, for a user who wishes
to use it to input data, and occasionally insert a new row, at
different positions,
but on *all* worksheets, and then to copy a selection to all rows
in the worksheets.

At the moment, I have a macro, that I cannot seem to get to work as
I would like it to.

As stated in my previous post, the enclosed macro groups all the
sheets, and inserts a new row, but only on the first worksheet.

Sheets(Array("Eng", "Scot", "Wales")).Select
Selection.EntireRow.Insert
ActiveWindow.SmallScroll ToRight:=19
Range("AD5:AE5").Select
Selection.AutoFill Destination:=Range("AD5:AE593"),
Type:=xlFillDefault Range("AD5:AE593").Select
ActiveWindow.SmallScroll ToRight:=-19
ActiveWindow.ScrollRow = 5
Range("D5").Select
End Sub

Can I please ask?

Is what I am trying to do achievable?
Should enclosed macro work?
If not, can someone please help me to change the macro, as needed?

Many thanks

George

B. R.Ramachandran wrote:
Hi,

You can insert a new row in several sheets at the SAME position
without having to invoke a macro.

Select all the sheets where you want to insert a new row (you can
select multiple sheets by holding the CTRL button and clicking on
the sheet tabs at the bottom of the sheets), right-click on the
row number (near the left-side border of the sheet) where you
want to insert a new row and click 'Insert" in the drop-down
list. A new row is inserted in every sheet selected.
Now click on one of the sheet tabs to undo the multiple sheet
selection.

Regards,
B. R. Ramachandran

"George Gee" wrote:

Hi

I need help with the following macro:

I am grouping all the worksheets together, and selecting the
row, above which I wish to insert a new row.
I want to insert a new row on all sheets, the enclosed macro
at the moment is only inserting a new row on the first sheet.

Selection.EntireRow.Insert
Range("AD5:AE5").Select
Selection.AutoFill Destination:=Range("AD5:AE594"),
Type:=xlFillDefault Range("AD5:AE594").Select
ActiveWindow.ScrollRow = 5
Range("D5").Select

Can anyone help me with this?

George Gee

--

Dave Peterson