Hi. Just something to keep in mind about the use of "UsedRange"


200 LastRow = obj_Sheet11.UsedRange.Rows.Count
210 If LastRow 1 Then
220 obj_Sheet11.Range("A1:N" & LastRow).Copy


This "assumes" you have data in A1.
For example, if you only had data in A10:N12, the "UsedRange" would set
LastRow to 3. (The # or Rows). Then, you would be coping blank data
(A1:N3) to Sheet1.

Having said that...with 1 cells selected, Sort "assumes" the current
region. Would that help?

Sub Demo()
Dim Obj_Sheet1 As Object
Set Obj_Sheet1 = Worksheets("Sheet1")

Obj_Sheet1.Range("A1").Sort _
Key1:=Range("A2"), Order1:=1, _
Key2:=Range("B2"), Order2:=1, _
Key3:=Range("D2"), Order3:=1, _
Header:=1
End Sub


David wrote:
THANK YOU Dave and Tim, very much appreciated.


"Tim Williams" wrote:

Also need to replace xlUp and xlYes with their numeric values.

Tim

"David" wrote in message
...
Dave,
Thank you for the quick response. Unfortunately, I have just pieced this
bit of code together so not sure I follow. I re-wrote the section, but
still
get the same error. Is this how it should look?

235 With obj_Sheet1
.Range(.Range("A1"), .cells(.rows.Count, "N").End(xlup)).Sort _
Key1:=.Range("A2"), Order1:=1, _
Key2:=.Range("B2"), Order2:=1, _
Key3:=.Range("D2"), Order3:=1, _
Header:=xlYes
End With



"Dave Peterson" wrote:

This is a guess...

xlAscending doesn't mean anything without a reference to excel.

If you open excel, go into the VBE and hit ctrl-g to see the immediate
window,
you can type this and hit enter:

?xlAscending
You'll see that that constant is = 1.

?xldescending
and you'll see that it's = 2.

Kind of the same way that you'll see that
?xlpastevalues is -4163
(and you got that one!)

David wrote:
Hello All,
I am trying to write some late binding Excel automation code, but
having problems with line# 235 below. Error is Number 1004,
Application-defined or object-defined error.

Dim objXLApp As Object
Dim objXLBook As Object
Dim obj_Sheet1 As Object
Dim obj_Sheet11 As Object
Dim LastRow As Integer
Dim Rng As Object
Const xlAscending = 1
Const xlYes = 1

180 Set obj_Sheet1 = objXLBook. _
Worksheets("Sheet1")

190 Set obj_Sheet11 = objXLBook. _
Worksheets("Sheet11")

200 LastRow = obj_Sheet11.UsedRange.Rows.Count
210 If LastRow 1 Then
220 obj_Sheet11.Range("A1:N" & LastRow).Copy

230 obj_Sheet1.Range("A1:N1").PasteSpecial -4163

235 With obj_Sheet1
.Range(.Range("A1"), .Cells(.Rows.Count, "N").End(xlUp)).Sort _
Key1:=.Range("A2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Key3:=.Range("D2"), Order3:=xlAscending, _
Header:=xlYes
End With

240 obj_Sheet1.Range("1:1").autofilter

Thanks in advance for your assistance.
Dave
--

Dave Peterson