View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
HartJF HartJF is offline
external usenet poster
 
Posts: 7
Default 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?!?