Activate all sheets

  • Thread starter Thread starter George Gee
  • Start date Start date
G

George Gee

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
 
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
 
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
 
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.
 
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
 
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
 
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
 
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.
 
Back
Top