0

I get the 1004 runtime error for "Application Defined ... Defined"

and with some formatting adjustments

I get the 1004 runtime error "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

I have limited knowledge to why I am getting this error, also limited VBA coding knowledge. My code seems to be very similar in methodology to others around the web. Yes, my code is very inefficient please don't critic it unless that is part of the solution.

Everything works fine up till the .sort and then that line errors out.

I have left my other attempt in the code commented out, under 'SORTING, so everyone can have all the facts.

Sub Update()

Dim strCar As String
'Dim lastrow As Long
strcrit = "MAINT"

'Opening CSV
Workbooks.Open Filename:="G:\Common\Schedule Files\Workbook1.csv"
Workbooks.Open Filename:="G:\Common\Schedule Files\Workbook2.csv"

Workbooks("Combo.xlsm").Worksheets("SheetA1").Cells.ClearContents
Workbooks("Combo.xlsm").Worksheets("SheetB2").Cells.ClearContents

'Copying CSV to Workbook
Workbooks("Combo.xlsm").Worksheets("SheetA1").Range("A:I").Value = Workbooks("Workbook1.csv").Worksheets("Sheet1").Range("A:I").Value
Workbooks("Combo.xlsm").Worksheets("SheetB2").Range("A:I").Value = Workbooks("Workbook2.csv").Worksheets("Sheet2").Range("A:I").Value

'Close CSV
Workbooks("Workbook1.csv").Close False
Workbooks("Workbook2.csv").Close False

'AutoFilter
Workbooks("Combo.xlsm").Worksheets("Sheet1").Cells.Clear
Workbooks("Combo.xlsm").Worksheets("Sheet2").Cells.Clear

Workbooks("Combo.xlsm").Worksheets("SheetA1").Range("A:I").AutoFilter Field:=5, Criteria1:="=*" & strcrit & "*"
Workbooks("Combo.xlsm").Worksheets("SheetA1").Range("A:I").AutoFilter Field:=8, Criteria1:=">0"
Workbooks("Combo.xlsm").Worksheets("SheetA1").Range("A:I").SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks("Combo.xlsm").Worksheets("Sheet1").Range("A1")
Workbooks("Combo.xlsm").Worksheets("SheetB2").Range("A:I").AutoFilter Field:=5, Criteria1:="=*" & strcrit & "*"
Workbooks("Combo.xlsm").Worksheets("SheetB2").Range("A:I").AutoFilter Field:=8, Criteria1:=">0"
Workbooks("Combo.xlsm").Worksheets("SheetB2").Range("A:I").SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks("Combo.xlsm").Worksheets("Sheet2").Range("A1")


'SORTING
'Dim lastrow As Long
'lastrow = Cells(Rows.Count, 2).End(xlUp).Row
'Workbooks("Combo.xlsm").Worksheets("Sheet2").Range("A2:I" & lastrow).Sort Key1:=Range("B2:B" & lastrow), Order1:=xlAscending, Header:=xlNo

Worksheets("Sheet2").Range("A:I").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo
Worksheets("Sheet1").Range("A:I").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo

End Sub
  • There doesn't appear to be an end row in your range Range("A2:I") – user778123 Apr 11 '18 at 20:12
0

I tried to make your code a bit more manageable, but it's not tested

This relies on the UsedRange, so delete all empty rows from Combo.xlsm on all sheets

To determine if you have empty rows, on each sheet select cell A1, then press Ctrl + End


Option Explicit
Public Sub SortAndCopyCSVs()
    Const CRIT = "MAINT"
    Const CSVF1 = "G:\Common\Schedule Files\Workbook1.csv"
    Const CSVF2 = "G:\Common\Schedule Files\Workbook2.csv"

    Dim wbCSV1 As Workbook:     Set wbCSV1 = Workbooks.Open(Filename:=CSVF1)
    Dim wbCSV2 As Workbook:     Set wbCSV2 = Workbooks.Open(Filename:=CSVF2)
    Dim wbCMBO As Workbook:     Set wbCMBO = Workbooks("Combo.xlsm")
    Dim wsCSV1 As Worksheet:    Set wsCSV1 = wbCSV1.Worksheets("Sheet1")
    Dim wsCSV2 As Worksheet:    Set wsCSV2 = wbCSV2.Worksheets("Sheet2")
    Dim wsA1 As Worksheet:      Set wsA1 = wbCMBO.Worksheets("SheetA1")
    Dim wsB2 As Worksheet:      Set wsB2 = wbCMBO.Worksheets("SheetB2")
    Dim wsS1 As Worksheet:      Set wsS1 = wbCMBO.Worksheets("Sheet1")
    Dim wsS2 As Worksheet:      Set wsS2 = wbCMBO.Worksheets("Sheet2")
    'Copy CSVs to Workbook Getline, and close CSVs
    Dim lr1 As Long:            lr1 = wsCSV1.UsedRange.Rows.Count
    Dim lr2 As Long:            lr2 = wsCSV2.UsedRange.Rows.Count
    Dim urA1AI As Range:        Set urA1AI = wsA1.Range("A1:I" & lr1)
    Dim urB2AI As Range:        Set urB2AI = wsB2.Range("A1:I" & lr2)
    wsA1.UsedRange.Cells.Clear: wsB2.UsedRange.Cells.Clear
    urA1AI.Value2 = wsCSV1.Range("A1:I" & lr1).Value2:       wbCSV1.Close False
    urB2AI.Value2 = wsCSV2.Range("A1:I" & lr2).Value2:       wbCSV2.Close False
    'AutoFilter and Copy
    wsS1.UsedRange.Cells.Clear: wsS2.UsedRange.Cells.Clear
    wsA1.UsedRange.AutoFilter Field:=5, Criteria1:="=*" & CRIT & "*"
    wsA1.UsedRange.AutoFilter Field:=8, Criteria1:=">0"
    wsA1.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=wsS1.Range("A1")
    wsB2.UsedRange.AutoFilter Field:=5, Criteria1:="=*" & CRIT & "*"
    wsB2.UsedRange.AutoFilter Field:=8, Criteria1:=">0"
    wsB2.UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=wsS2.Range("A1")
    'Sort
    wsS1.UsedRange.Columns("A:I").Sort Key1:=wsS1.UsedRange.Columns("B"), Header:=xlNo
    wsS2.UsedRange.Columns("A:I").Sort Key1:=wsS2.UsedRange.Columns("B"), Header:=xlNo
End Sub
|improve this answer|||||
  • Thank you I will look into this today and transition to this "optimized" (not sure it that's the right word) version. At a glance I see I have much to learn, thank you! – Loading... Apr 13 '18 at 12:26
  • This seemed to do the trick. I'm not exactly sure where the issue stemmed from. Wow, Thanks! Much appreciated. – Loading... Apr 13 '18 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.