Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste to array of sheets
Please check and identify the sheets getting assigned to the array
shArr...within the loop If this post helps click Yes --------------- Jacob Skaria "J.W. Aldridge" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste to array of sheets
These are the sheets I need the range posted to.
Nothing changed. Set FirstSheet = Sheets("ta_start") Set LastSheet = Sheets("tb_end") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Array | Excel Discussion (Misc queries) | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Paste Array to .xls : All at once | Excel Programming | |||
Paste from Array to a Row | Excel Programming | |||
Copy Range and Paste to Array of Sheets | Excel Programming |