Activate all sheets
 

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

Activate all sheets
 

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Rows("5:6").Select
Selection.Insert Shift:=xlDown 'inserts 2 rows
Sheets("Sheet1").Select

Just replace Sheet1 etc with your sheet names.

Hope it helps.


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=490233

Activate all sheets
 

goober

Thanks for your response, however!

I now have:

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


This worked perfectly for me the first time that I ran it,
but then it now only inserts a new row on the first worksheet
again!


Baffled.

George Gee


"goober" wrote in
message ...

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Rows("5:6").Select
Selection.Insert Shift:=xlDown 'inserts 2 rows
Sheets("Sheet1").Select

Just replace Sheet1 etc with your sheet names.

Hope it helps.


--
goober
------------------------------------------------------------------------
goober's Profile:
http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=490233

Activate all sheets
 

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

Activate all sheets
 

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

Activate all sheets
 

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

Activate all sheets
 

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

Activate all sheets
 

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

Activate all sheets
 

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

Activate all sheets
 

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

Activate all sheets
 

Dave

That's the one!
Can't fault it.

Long may you select...

George




Dave Peterson wrote:
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