Pass array of sheet names to subroutine
 

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
.... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?

Pass array of sheet names to subroutine
 

One place is avarSheetSub and the other is just avarSheet.

"HartJF" wrote:

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?

Pass array of sheet names to subroutine
 

I call the subroutine from a frm_click routine. I have used both identical
and differentiated names in testing. I differentiated the names here to
avoid confusion.

"JLGWhiz" wrote:

One place is avarSheetSub and the other is just avarSheet.

"HartJF" wrote:

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?

Pass array of sheet names to subroutine
 

Disregard that. Did you try it with Parentheses

sbrInsertRow(avarSheet)

"HartJF" wrote:

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?

Pass array of sheet names to subroutine
 

No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or
sbrInsertRow avarSheet()

"JLGWhiz" wrote:

Disregard that. Did you try it with Parentheses

sbrInsertRow(avarSheet)

"HartJF" wrote:

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?

Pass array of sheet names to subroutine
 

try sbrInsertRow(avarSheet(i))

"HartJF" wrote:

No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or
sbrInsertRow avarSheet()

"JLGWhiz" wrote:

Disregard that. Did you try it with Parentheses

sbrInsertRow(avarSheet)

"HartJF" wrote:

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?

Pass array of sheet names to subroutine
 

That would insert a row on one sheet at a time. My problem is that, since
Sheet2 is an accumulating cover sheet, if I don't insert as a group, the
summation formula is destroyed. Sheet2!C15 contains =sum(Sheet3:SheetN!C15),
where SheetN is the final sheet. Inserting a row at Sheet3!5:5 makes
Sheet2!C15 contain =#REF! I think I need to group Sheet3:SheetN and then
insert row 5. I need this in a subroutine because the statement after the
calling statement is:

sbrInsertRow "Sheet2"

"JLGWhiz" wrote:

try sbrInsertRow(avarSheet(i))

"HartJF" wrote:

No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or
sbrInsertRow avarSheet()

"JLGWhiz" wrote:

Disregard that. Did you try it with Parentheses

sbrInsertRow(avarSheet)

"HartJF" wrote:

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?

Pass array of sheet names to subroutine
 

I think I discovered the answer (at least it works!)

When I call
sbrInsertRow avarSheet()
I am sending just one parameter, an array of variants. When the subroutine
receives
Sub sbrInsertRow(avarSheetSub as Variant)
it receives one parameter, a variant containing an array. I can still refer
to individual elements in the receiving array
Debug.Print avarSheetSub(0)
or I can refer to the entire array
Worksheets(avarSheetSub).Select

This is terribly confusing, but I found a helpful strand at
http://www.mrexcel.com/archive2/13000/14499.htm

Thanks for your help JLGWhiz!

"JLGWhiz" wrote:

try sbrInsertRow(avarSheet(i))

"HartJF" wrote:

No luck with sbrInsertRow(avarSheet), sbrInsertRow(avarSheet()), or
sbrInsertRow avarSheet()

"JLGWhiz" wrote:

Disregard that. Did you try it with Parentheses

sbrInsertRow(avarSheet)

"HartJF" wrote:

I want to insert a row in one or more adjacent sheets. I have written a
subroutine:

Sub sbrInsertRow(ParamArray avarSheetSub() As Variant)
With Worksheets(avarSheetSub())
.Select
.Rows(5).Insert
... formatting instructions ...
End With
End Sub

I call the subroutine, sending the names of Sheet 3 to the end:

intSheet=Worksheets.Count-3
For i = 0 To intSheet
avarSheet(i) = Worksheets(i + 3).Name
Next
sbrInsertRow avarSheet

At a breakpoint on the calling line, debug.print correctly enumerates
avarSheet, but at a breakpoint on the With Worksheets line of the sub,
avarSheetSub is empty.

I know I've done something wrong, but what?!?