Simple Worksheet.Range issue

[JimmyMack]

I keep getting a "method 'range' of object '_worksheet' failed" error when I reach the line in red. Ideas?


Sub UpdateMasterSheet()
'
' UpdateMasterSheet Macro
' Updates master worksheet from individual staff sheets
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Dim MasterSheet As Worksheet
Dim iStaff As Integer, iRow As Integer
Dim StaffName As Variant
Dim iCell As Range

Set MasterSheet = Sheets("May- July 2012")

For i = 0 To 13
iStaff = i
iRow = (i + 1) * 3
StaffName = Array("Rochelle", "Cassie", "Bryan", "Rich", "Bill", "Kelly", _
"Steve", "Garrett", "Ena", "Cindy", "Matt", "John", "New Person", _
"Brent Waters")

Sheets(StaffName(iStaff)).Select
Range("D3:GZ4").Copy
MasterSheet.Select
MasterSheet.Range(Cells(iRow, 4)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:= False, Transpose:=False
Next i
End Sub

 

[rexcel]

change line to

MasterSheet.Cells(iRow, 4).Select


-Rex

 

[JimmyMack]

Resolved: Simple Worksheet.Range issue

Worked around the issue using this code:

Sub UpdateMasterSheet()
'
' UpdateMasterSheet Macro
' Updates master worksheet from individual staff sheets
'
' Keyboard Shortcut: Ctrl+Shift+U
'
Dim MasterSheet As Worksheet
Dim iStaff As Integer, iRow As Integer
Dim StaffName As Variant
Dim iCell As Range

Set MasterSheet = Sheets("May- July 2012")

For i = 0 To 13
iStaff = i
iRow = (i + 1) * 3
StaffName = Array("Rochelle", "Cassie", "Bryan", "Rich", "Bill", "Kelly", _
"Steve", "Garrett", "Ena", "Cindy", "Matt", "John", "New Person", _
"Brent Waters")

Sheets(StaffName(iStaff)).Select
Range("D3:GZ4").Copy
MasterSheet.Select
Cells(iRow, 4).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:= False, Transpose:=False
Next i
End Sub



I'd still like to know why the original didn't work, though, if anyone has a minute

 

[JimmyMack]

Thanks Rex! Makes sense!