Paste to array of sheets

J

J.W. Aldridge

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
 
J

Jacob Skaria

Please check and identify the sheets getting assigned to the array
shArr...within the loop

If this post helps click Yes
 
J

J.W. Aldridge

These are the sheets I need the range posted to.
Nothing changed.

Set FirstSheet = Sheets("ta_start")
Set LastSheet = Sheets("tb_end")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top