Here is an easy one, but I just can't get it right. I trying to sort data in
a file just opened. But when it get to the sort part I get an "informative"
400 error.

Sub GetDataFromFCDBFile()
Dim wb As Workbook
Set wb = Workbooks.Open("H:\myfolder\MyFile.xls", True, False, , , , True)
'wb.Worksheets("ProdBatch").Cells.copy
Destination:=ThisWorkbook.Worksheets("Bulk Batch Lookup").Range("A1")

With wb.Worksheets("ProdBatch").Cells
.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
End With
End Sub

Anyone?
The Doctor

Doctor,

.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _

Should be

.Sort Key1:=.Range("A2"), Order1:=xlAscending, Key2:=.Range("B2") _


Note the dot before the Range - that makes the key range part of the sorted range, otherwise it is a
range on the default worksheet.

HTH,
Bernie
MS Excel MVP


"Dr. Schwartz" wrote in message
...
Here is an easy one, but I just can't get it right. I trying to sort data in
a file just opened. But when it get to the sort part I get an "informative"
400 error.

Sub GetDataFromFCDBFile()
Dim wb As Workbook
Set wb = Workbooks.Open("H:\myfolder\MyFile.xls", True, False, , , , True)
'wb.Worksheets("ProdBatch").Cells.copy
Destination:=ThisWorkbook.Worksheets("Bulk Batch Lookup").Range("A1")

With wb.Worksheets("ProdBatch").Cells
.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
End With
End Sub

Anyone?
The Doctor

Not so simple after all to a VBA novice I guess - thanks a lot Bernie!

"Bernie Deitrick" wrote:

Doctor,

.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _

Should be

.Sort Key1:=.Range("A2"), Order1:=xlAscending, Key2:=.Range("B2") _


Note the dot before the Range - that makes the key range part of the sorted range, otherwise it is a
range on the default worksheet.

HTH,
Bernie
MS Excel MVP


"Dr. Schwartz" wrote in message
...
Here is an easy one, but I just can't get it right. I trying to sort data in
a file just opened. But when it get to the sort part I get an "informative"
400 error.

Sub GetDataFromFCDBFile()
Dim wb As Workbook
Set wb = Workbooks.Open("H:\myfolder\MyFile.xls", True, False, , , , True)
'wb.Worksheets("ProdBatch").Cells.copy
Destination:=ThisWorkbook.Worksheets("Bulk Batch Lookup").Range("A1")

With wb.Worksheets("ProdBatch").Cells
.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:= _
xlSortNormal
End With
End Sub

Anyone?
The Doctor