Insert row on multiple sheets

[HartJF]

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:

Worksheets(avarSheet).Select
Selection.Rows(intRow).Select
With Selection
.EntireRow.Insert
*** 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?

 

[OssieMac]

Hi Hart,

The following line returns you to only one sheet selected.
Worksheets(avarSheet).Select

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

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

Regards,

OssieMac

 

[HartJF]

Thanks for the response, OssieMac. We're part of the way to a solution.

I inserted the statement

Sheets(avarSheet(0)).Activate

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

 

[OssieMac]

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


Regards,

OssieMac

 

[OssieMac]

Hi yet again,

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

Selection.Rows(intRow).EntireRow.Select
With Selection
.Insert
End With

Regards,

OssieMac

 

[OssieMac]

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
Rows(intRow).Select
With Selection
.Insert
End With

Regards,

OssieMac

 

[Dave Peterson]

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)
wks.Rows(myRow).Insert
Next wks

End Sub