Method Range of Object Worksheet failed error

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Greetings,

I am receiving the "method Range of object worksheet failed" error on the line below within the context of the short script further below.

Code:
.Range(h, h.End(xlToRight)).Copy

Code:
For Each ws In COM.Worksheets

    With ws
    
        k = .Cells(Rows.Count, "A").End(xlUp).Row
    
        Do Until k = 2
            If .Cells(k, b) <> "" Then
                Set h = .Cells(k, b)
                k = k - 1
           Else
               .Range(h, h.End(xlToRight)).Copy
               .Cells(k, b).PasteSpecial Paste:=xlPasteValues
               k = k - 1
           End If
        
        Loop
    
    End With
    
    Next ws

I suppose the error is referring to the "Copy" method used with the Range Object as I have it structured (using the "with" statement)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I have a theory, but it's not easy to prove on my end. From your code, I assume you're skilled so I'll throw it out there.

It seem like your line
Code:
[COLOR=#333333].Range(h, h.End(xlToRight)).Copy[/COLOR]
exposes you to the possibility that .range and the range set to h are not on the same sheet... this would cause an issue. I suspect that the problem with your code is that while you tried to keep it concise by using the with and setting the h range, your problem is actually disguised by these somehow. if you went and explicitly defined each range the problem would likely stick out.
 
Upvote 0
Which line of code causes the error?
 
Upvote 0
=ODIN=

I was asking the OP to clarify since they posted 'I suppose'.:)

If that line is the problem, and it probably is, I'm wondering if the problem might be caused by h not being set.

PS If it is set, as far as i can see, it will refer to a range on ws.
 
Upvote 0
Thanks for the help...Correct the line below is the line identified with the error.

Code:
[COLOR=#333333].Range(h, h.End(xlToRight)).Copy[/COLOR]

I should clarify. There are several similarly arranged worksheets within the workbook that this code runs in. For some reason the error is recorded at a worksheet in the middle of the set of worksheets in the workbook after about half of the sheets have run smoothly and were not found to be in error.

Also, the 'b' integer value used to define the column used in setting 'h' was set using another worksheet. I don't suspect this affects anything since many of the sheets have already run to completetion.
 
Upvote 0
After looking at the processed sheets and unprocessed sheet I found a difference and I think I know whats going on...its going to take a bit to rewrite it and test it. but as soon as I do I will put together a follow up post.
 
Upvote 0
I agree with =ODIN= and Norie, and it looks like the problem is here:

Code:
[COLOR=#ff0000][B]If .Cells(k, b) <> "" Then
     Set h = .Cells(k, b)
[/B][/COLOR]     k = k - 1
Else
    .Range(h, h.End(xlToRight)).Copy
    .Cells(k, b).PasteSpecial Paste:=xlPasteValues
    k = k - 1
End If

If .Cells(k,b) is blank, then you won't be setting h based on the current worksheet, ws, and it will still point to the previous worksheet.

Hence you will get an error in this line of code:

Code:
.Range(h, h.End(xlToRight)).Copy
because .Range and h will be pointing to different worksheets.
 
Upvote 0
I have it running now. the "k" object was incorrectly written. I substituted the "A" for a b and it caused problems due to the layout of the sheets (a combination of other worksheets, some with differences in # of rows transferred.

Thanks for the help, apologies for the dull ending and my error
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top