A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Activate all sheets



 
 
Thread Tools Display Modes
  #1  
Old December 2nd, 2005, 06:57 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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





  #2  
Old December 2nd, 2005, 07:32 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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

  #3  
Old December 2nd, 2005, 08:33 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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



  #4  
Old December 3rd, 2005, 01:07 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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






  #5  
Old December 3rd, 2005, 09:32 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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



  #6  
Old December 3rd, 2005, 05:27 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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
  #7  
Old December 3rd, 2005, 07:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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



  #8  
Old December 3rd, 2005, 08:14 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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
  #9  
Old December 3rd, 2005, 08:46 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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




  #10  
Old December 3rd, 2005, 09:22 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofilter Lists across Multiple Sheets, Maintain Correct Referenc EDSTAFF Worksheet Functions 0 November 14th, 2005 04:27 PM
In 3 active sheets in wkbk, determine& display the # of sheets that have data wrpalmer General Discussion 1 November 4th, 2005 03:01 PM
How do you activate Office 2003 by phone? Vanguard10 General Discussions 2 May 9th, 2005 07:51 PM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Worksheet Functions 1 March 15th, 2005 06:49 PM
(¯`·._.·[ Extract Data From Multiple Sheets ]·._.·´¯) hyperdreamz General Discussion 1 September 29th, 2004 04:03 PM


All times are GMT +1. The time now is 12:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.