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