Another Application Defined or Object Defined Error

[SBF12345]

Greetings,

I am receiving an application or object defined error on the below line.

h is a range variable, AC is a string, k and b are integers

 COM.Worksheets(AC).Range(h, h.End(xlToRight)).Select

 For Each ws In COM.Worksheets    
    AC = ws.Name
    
    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
    
        COM.Worksheets(AC).Range(h, h.End(xlToRight)).Select
        Selection.Copy
        COM.Worksheets(AC).Range(Cells(k, b)).PasteSpecial Paste:=xlPasteValues
        
        k = k - 1
        
        End If
        
        Loop

 

[Kenneth Hobson]

One part is not right. Since you did not show Set COM for a workbook, that would be my guess.

 

[StephenCrump]

There's at least a couple of things going wrong here ...

You can't select a range on another worksheet without first activating the sheet. But you actually don't need to use .Select in the first case. It's more efficient to:

'replace
COM.Worksheets(AC).Range(h, h.End(xlToRight)).Select
Selection.Copy
        
'with
COM.Worksheets(AC).Range(h, h.End(xlToRight)).Copy

You have unqualified references to Cells which will always refer to the ActiveSheet. It looks like you actually need:

k = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set h = ws.Cells(k, b)
etc

That should fix your object error. At the moment h is pointing to the ActiveSheet, so your code will error if you try to locate it on a different sheet:

COM.Worksheets(AC).Range(h, h.End(xlToRight))

Incidentally, you can replace Worksheets(AC) with ws.

 

[Norie]

Try this.

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

 

[SBF12345]

Sorry for the late reply,

Thanks for all the help! It seems to be running smoothly now, minus a glitch I have yet to investigate. using the above script design (the with ws statement) the script is going from worksheet to worksheet, but gets hung up on a seemingly random worksheet 10th of about 20 in the workbook and gives an error "range object of _worksheet failed". Any ideas? I will post the specific line highlighted at the error when I get more time in the next day or two. Thanks again!