Solved: Method 'Range' of object '_Global' failed

[lukecj]

I continue to receive this error when running the below code IF the activesheet is not Sheet6, but when I execute it with Sheet6 as the activesheet, it works. I know I am probably not defining "RecordNumber" correctly. Any ideas? Would greatly appreciate the help. I have been googling for an hour and a half. Thanks.

[VBA]Sub MonsterProject()

Dim RecordNumber As Long

Application.ScreenUpdating = False

Sheet6.[infinity_mean].End(xlToLeft).Offset(0, 1).Value = Sheet2.[security].Value

RecordNumber = Sheet6.Range("ref_mean", Range("ref_mean").End(xlToRight)).Count - 1

Sheet6.[Paste_Mean].Offset(0, RecordNumber).Value = Sheet16.[Saved_Mean].Value
Sheet6.[paste_OneSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_OneSDev].Value
Sheet6.[paste_TwoSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_TwoSDev].Value
Sheet6.[paste_ThreeSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_ThreeSDev].Value
Sheet6.[paste_Scenario1].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario1].Value
Sheet6.[paste_Scenario2].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario2].Value
Sheet6.[paste_Scenario3].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario3].Value
Sheet6.[paste_Scenario4].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario4].Value
Sheet6.[paste_Scenario5].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario5].Value

Application.ScreenUpdating = True

End Sub[/VBA]

[Bob Phillips]

[vba]


Sub MonsterProject()

Dim RecordNumber As Long

Application.ScreenUpdating = False

With Sheet6

.[infinity_mean].End(xlToLeft).Offset(0, 1).Value = Sheet2.[security].Value

RecordNumber = .Range("ref_mean", .Range("ref_mean").End(xlToRight)).Count - 1

.[Paste_Mean].Offset(0, RecordNumber).Value = Sheet16.[Saved_Mean].Value
.[paste_OneSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_OneSDev].Value
.[paste_TwoSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_TwoSDev].Value
.[paste_ThreeSDev].Offset(0, RecordNumber).Value = Sheet16.[saved_ThreeSDev].Value
.[paste_Scenario1].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario1].Value
.[paste_Scenario2].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario2].Value
.[paste_Scenario3].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario3].Value
.[paste_Scenario4].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario4].Value
.[paste_Scenario5].Offset(0, RecordNumber).Value = Sheet16.[saved_Scenario5].Value
End With

Application.ScreenUpdating = True

End Sub
[/vba]

[lukecj]

Xld - you sir are a man among boys. Thanks once again for your help.

[lukecj]

This is a really stupid question, but I have not been on the forum for a while and, for the life of me, I cannot figure out how to mark this thread as solved. help??????

[Bob Phillips]

It is in Thread Tools at the top of the threads.

[lukecj]

All I get are three options in Thread Tools:

Show Printable Version
Email this Page
Unsubscribe from this Thread

No mark as solved...

[Bob Phillips]

As the instigator of the thread you should get it. I think there may be a problem on Chrome if you are using that, but I have administrator privileges so I did it for you.