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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>
>
>
>
>

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

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

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

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

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

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

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