Excel - Insert row on multiple sheets

Asked By HartJ on 28-Sep-07 12:06 AM
I need to insert a row on multiple adjacent sheets.  I need to do it as a
group because I have a 3-D summary sheet whose reference must remain aligned.
The active cell on each sheet may be different.  I want to insert the row at
the location of the active cell on the active sheet.  Here's what I've done:

With Selection
*** formatting instructions ***
End With

avarSheet is a variant containing an array, which is properly populated with
the names of the sheets to which the row should be added.  The worksheets are
successfully selected as a group.  intRow contains the number of the row of
the active cell on the active worksheet.

When I run this code, a row is inserted only on Sheet avarSheet(0), and at
the current row of Sheet avarSheet(0) plus intRow.

What am I doing wrong?

OssieMa replied on 28-Sep-07 12:34 AM
Hi Hart,

The following line returns you to only one sheet selected.

You need something like this:-
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

Sheets("Sheet1").Activate 'becomes you main sheet to work on.


HartJ replied on 28-Sep-07 08:34 AM
Thanks for the response, OssieMac.  We're part of the way to a solution.

I inserted the statement


between the Worksheets... and Selection... statements in the original post.
All avarSheets are selected.  The active cell in all avarSheets became the
active cell in avarSheet(0) offset by intRow.  avarSheet(0) was the only
sheet on which the row was insereted.

The calling procedure assembles an array of sheets:

Dim avarSheet() As Variant, i As Integer, intSheet As Integer
intSheet = Worksheets.Count - 3
ReDim avarSheet(intSheet) As Variant
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
sbrInsertRow (avarSheet())

The subroutine is defined:

Sub sbrInsertRow(avarSheet As Variant)

If I set a breakpoint on the With statement in the original post of the
subroutine, the sheets named in the array are grouped.

What am I missing?
OssieMa replied on 28-Sep-07 05:57 PM
Hi again

Firstly I have been guilty of no reading the post properly. Again!!!.  My
apologies for that. However, I did some testing and it appears to work
without the EntireRow.

Modify as per the following:-

With Selection
End With

I'll be interested in your result because I have no idea why it does not
like the EntireRow. Your code appears to be selecting the row anyway.


OssieMa replied on 28-Sep-07 11:36 PM
Hi yet again,

I have had another look at this and I think that EntireRow should be with
your row selection.

With Selection
End With


OssieMa replied on 29-Sep-07 12:20 AM
Hi again Hart,

This has had me so intrigued that I couldn't leave it alone. This is what I
have come up with.

Selection.Rows(intRow).Select    'This only selects one cell.

Selection.Rows(intRow).EntireRow.Select    'Selects the entire row.

Rows(intRow).Select    'Selects the entire row. No 'Selection' or 'EntireRow'

intRow = 10
With Selection
End With


Dave Peterson replied on 29-Sep-07 08:37 AM
You want to insert rows based on the activesheet's activecell?

Option Explicit
Sub testme()

Dim WksNames As Variant
Dim wks As Worksheet
Dim myRow As Long
Dim IntRow as long

IntRow = 5

'you may not want to add one.  I figured that if IntRow was 0, then you'd
'want to insert after the activecell's row--but maybe you don't.
myRow = ActiveCell.Row + 1 + IntRow

'my array of sheet names
WksNames = Array("Sheet1", "sheet2", "sheet3")

For Each wks In Worksheets(WksNames)
Next wks

End Sub


Dave Peterson