View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
J.W. Aldridge J.W. Aldridge is offline
external usenet poster
 
Posts: 425
Default Paste to array of sheets

I've been using this code for over a year, all of a sudden, I'm
getting the following error on the line " Sheets(shArr).Select".

Error Message: Run Time Error, Select method of sheets class failed.

Any suggestions (on replacing code or fixing it).

Thanx


Sub Grp_and_paste_labels_on_sheets()

'SELECT DATA TO PLACE ON SHEETS
Sheets("ERROR COUNTER").Select
Range("a1:i1").Select
Selection.Copy

Dim Sh As Object
Dim shArr() As String
Dim FirstSheet As Object
Dim LastSheet As Object

'SET RANGE OF SHEETS TO BE GROUPED
Set FirstSheet = Sheets("ta_start")
Set LastSheet = Sheets("tb_end")


ReDim shArr(FirstSheet.Index To LastSheet.Index)


For Each Sh In ThisWorkbook.Sheets
If Sh.Index = FirstSheet.Index And Sh.Index <=
LastSheet.Index Then
shArr(Sh.Index) = Sh.Name
End If

Next Sh


Sheets(shArr).Select

'PASTE DATA ONTO GROUPED SHEET SELECTION
Range("a1").Select
ActiveSheet.Paste
Range("E12:E13").Select
Range("E13").Activate



End Sub