Can't cause of Method 'Range of Object"_Global failed.

[RSJohnson]

I am trying to define where data copied from one worksheet should be placed on another. I am able to set all other variables except this one. The variable is dimensioned only in the sub where it is located. As I F8 through the sub I get a Runtime error 1004;: Method ‘Range of Object’_Global failed.

The code below is my attempt to set my variables. The Set rngDestin is where the error occurs.

No clue what to look for a or where to start. Can someone suggest possible cause of error?

Sub MatchPCA()
'
' MatchPCA Macro
' Macro recorded 2/20/2004 by Robert S. Johnson

Dim intCounter As Integer, intStop As Integer
Dim rngGMPCA As Range, rngDAFRPCA As Range, rngDestin As Range, rngToCopy As Range

'This initializes the variable for the first match.
Set rngDAFRPCA = Range(Worksheets("Prepared_Expense").Range("Q6").Offset(1, -15))
Set rngGMPCA = Range(Worksheets("First QT").Range("B4")) 'Change to variable!
Set rngToCopy = Worksheets("First QT").Range("CB5:CE5") 'Change to variable!
Set rngDestin = Range(Worksheets("Prepared_Expense").Range("R6"))

 

[PHV]

Set rngDestin = Range(Worksheets("Prepared_Expense").Range("R6"))
Why not just this ?
Set rngDestin = Worksheets("Prepared_Expense").Range("R6")

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi semaphore,

Too many Ranges ..

[blue]Set rngDestin = Worksheets("Prepared_Expense").Range("R6")[/blue]

The two range functions are referencing different sheets (one implicit, one explicit) and causing Excel a conflict which it can't resolve - the first is unnecessary.

After writing this, I went back and looked at your code again and see you have the same construct earlier, so I can't be quite right if the first one works - but give it a try anyway

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[RSJohnson]

Thanks PH and Tony followed your advice and it worked.