Dynamic Sorting, With a Macro On Open. For a Excel Spreedsheet

I am using Office 2013. I am trying to create a macro that will automatically sort Column B Starting from B11 And ending 5 lines from the last line in the spreadsheet. With 3 blank Rows. I currently have this macro made for it.
Sub Sort()
'
' Sort Macro
' To Sort PMIX for CDM
'

'
    
    Range("A11:I70").Select
    ActiveWorkbook.Worksheets("Counter.pmx").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Counter.pmx").Sort.SortFields.Add Key:=Range( _
        "B12:B70"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "AU LAIT,SM AU LAIT,MD AU LAIT,LG AU LAIT,BLACK COFFEE,SM BLACK COFFEE,MD BLACK COFFEE,LG BLACK COFFEE,MOCHA,SM MOCHA,MD MOCHA,LG MOCHA,HOT CHOCOLATE,SM HOT CHOCOLATE,MD HOT CHOCOLATE,LG HOT CHOCOLATE,CONTINENTAL,SM CONTINENTAL,MD CONTINENTAL,LG CONTINENTAL,AMERICAN,SM AMERICAN,MD AMERICAN,LG AMERICAN,O.J.,SM O.J.,MD O.J.,LG O.J.,ICED TEA,WHITE MILK,SM WHITE MILK,LG " & _
        "LK,CHOCOLATE MILK,SM CHOCO MILK,LG CHOCO MILK,BOTTLE WATER,ICE WATER,BEIGNETS-1 ORDER,BEIGNETS-2 ORDER,BEIGNETS-3 ORDER,BEIGNETS-4 ORDER,FROZEN AU LAIT,SM FROZ AU LAIT,LG FROZ AU LAIT,FROZEN MOCHA,SM FROZ MOCHA,LG FROZ MOCHA,ADD PUMP CHOCO,SM SODA,MD SODA,LG SODA,COKE,DIET COKE,DR PEPPER,SPRITE,ROOT BEER,FRUIT PUNCH,OTHER DRINK,C/O 96 HOT,C/O 96 MOCHA,C/O 96 ICE,SOD" & _
        "C/O 96 ICE MOCHA,AIRPOT,AIRPOT REFILL,C/O 96 FROZEN,WALKER SPECIAL,REG CAN COFFEE,DECAF CAN COFFEE,FR ROAST CAN COF,BOX MIX,WHT LOGO APRON,LOGO T SHIRT,SOUVENIR MUG,SOUVENIR MUG SPL,SOUVENIR MUG PK,SOUVENIR MUG 1BK,SOUVENIR MUG 2BK,ARCHWAY MUG,ARCHWY MUG SPECL,ARCH MUG PACKAGE,ARCH MUG 1 BASK,ARCH MUG 2 BASK,LOGO MUG,LOGO MUG SPECIAL,LOGO MUG PACKAGE,LOGO MUG 1 BASK" & _
        " 2 BASK,DINER MUG,DINER MUG SPECL,DINER MUG PACK,DINER MUG 1 BASK,DINER MUG 2 BASK,TERVIS MUG,TERVIS 16 oz TUMBLER,TERVIS 24 oz TUMBLER,GREEN STRIPE MUG,GREEN STRIPE SPCL,GREEN STRIPE PACK,GREEN STRIPE 1 BASK,GREEN STRIPE 2 BASK,C/O 96 FRZN MOCHA,COLOSSAL MUG,COLSSAL MUG SPCL,COLSSAL MUG PACK,COLSAL MUG 1 BAS,COLSAL MUG 2 BAS,DANCER MUG,DANCER MUG SPECL,DANCER MUG P" & _
        "R MUG 1 BAS,DANCER MUG 2 BAS,FLEUR DE LIS,FLR D LS SPL,FLR D L PACK,FR D LS 1 BK,FR D LS 2 BK,CLEAR TUMBLER,CLEAR TUMBL SPCL,WATER COLOR MUG,WATER COLOR SPCL,WATER COLOR PACK,1 WTR COLOR BSKT,2 WTR COLOR BSKT,C/O 160 HOT,C/O 160 MOCHA,VIBRANT MUG,VIBRANT SPECIAL,VIBRANT PACKAGE,VIBRANT 1 BASK,VIBRANT 2 BASK,LG LOGO MUG,LG LOGO MUG SPCL,LG LOGO PACKAGE,LG LOGO 1 BASK" & _
        "O 2 BASKET,LOGO SHOT GLASS,LOGO TRAVEL MUG,LOGO TVL MUG SPL,C/O 160 ICE,MINI LOGO MUG,MINI WHITE MUG", DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Counter.pmx").Sort
        .SetRange Range("A11:I70")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window




I would like to run this sort at open and that the ending field of the sort to be dynamic.

I have attached a example spread sheet the example spreadsheet changes size every day.
CounterBlank.pmx.csv
Fradojr88Asked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
What i mean is this essentially feeding in your code the sorting order and then applying the same in your report..

Enclosed is the code and workbook for your reference...

Sub getdata()
    Dim wb As Workbook, xpath As String
    Dim wb1 As Workbook
    Set wb = ThisWorkbook

    xpath = "C:\Users\Fradosta\Desktop\Counter.pmx.csv"
    Dim r As Range, cell As Range
    Dim lr As Long, ws1 As Worksheet, z As Long

    Set ws1 = Sheets("Sheet1")
    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set r = ws1.Range("B1:B" & lr)
    z = Application.WorksheetFunction.Max(r)

    Set r = ws1.Range("A1:B" & lr)


    Set wb1 = Workbooks.Open(xpath)

    Dim rng As Range, lrow As Long
    Dim ws As Worksheet

    Set ws = Sheets("Counter.pmx")

    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = ws.Range("a11:j" & lrow)

    ws.Range("J11:J" & lrow).Formula = "=IFERROR(VLOOKUP(B11,'[" & ThisWorkbook.Name & "]Sheet1'!$A:$B,2,0) ," & z + 1 & ")"
    ws.Range("J11:J" & lrow).Value = ws.Range("J11:J" & lrow).Value
    ws.Sort.SortFields.Clear

    ws.Sort.SortFields.Add Key:=Range("J11:J" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ws.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("J11:J" & lrow).Value = ""
End Sub

Open in new window


Workbook...
Book1.xlsm
0
 
Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for..

Sub sortdata()
    Dim rng As Range, lrow As Long
    Dim ws As Worksheet

    Set ws = Sheets("CounterBlank.pmx")

    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = ws.Range("a11:i" & lrow)

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("B11:B" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ws.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

Open in new window


Saurabh...
0
 
Fradojr88Author Commented:
I have modified the the code to the following and it works correctly.
Sub sortdata()
    Dim rng As Range, lrow As Long
    Dim ws As Worksheet

    Set ws = Sheets("Counter.pmx")

    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = ws.Range("a11:i" & lrow)

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("B11:B" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "AU LAIT,SM AU LAIT,MD AU LAIT,LG AU LAIT,BLACK COFFEE,SM BLACK COFFEE,MD BLACK COFFEE,LG BLACK COFFEE,MOCHA,SM MOCHA,MD MOCHA,LG MOCHA,HOT CHOCOLATE,SM HOT CHOCOLATE,MD HOT CHOCOLATE,LG HOT CHOCOLATE,CONTINENTAL,SM CONTINENTAL,MD CONTINENTAL,LG CONTINENTAL,AMERICAN,SM AMERICAN,MD AMERICAN,LG AMERICAN,O.J.,SM O.J.,MD O.J.,LG O.J.,ICED TEA,WHITE MILK,SM WHITE MILK,LG " & _
        "LK,CHOCOLATE MILK,SM CHOCO MILK,LG CHOCO MILK,BOTTLE WATER,ICE WATER,BEIGNETS-1 ORDER,BEIGNETS-2 ORDER,BEIGNETS-3 ORDER,BEIGNETS-4 ORDER,FROZEN AU LAIT,SM FROZ AU LAIT,LG FROZ AU LAIT,FROZEN MOCHA,SM FROZ MOCHA,LG FROZ MOCHA,ADD PUMP CHOCO,SM SODA,MD SODA,LG SODA,COKE,DIET COKE,DR PEPPER,SPRITE,ROOT BEER,FRUIT PUNCH,OTHER DRINK,C/O 96 HOT,C/O 96 MOCHA,C/O 96 ICE,SOD" & _
        "C/O 96 ICE MOCHA,AIRPOT,AIRPOT REFILL,C/O 96 FROZEN,WALKER SPECIAL,REG CAN COFFEE,DECAF CAN COFFEE,FR ROAST CAN COF,BOX MIX,WHT LOGO APRON,LOGO T SHIRT,SOUVENIR MUG,SOUVENIR MUG SPL,SOUVENIR MUG PK,SOUVENIR MUG 1BK,SOUVENIR MUG 2BK,ARCHWAY MUG,ARCHWY MUG SPECL,ARCH MUG PACKAGE,ARCH MUG 1 BASK,ARCH MUG 2 BASK,LOGO MUG,LOGO MUG SPECIAL,LOGO MUG PACKAGE,LOGO MUG 1 BASK" & _
        " 2 BASK,DINER MUG,DINER MUG SPECL,DINER MUG PACK,DINER MUG 1 BASK,DINER MUG 2 BASK,TERVIS MUG,TERVIS 16 oz TUMBLER,TERVIS 24 oz TUMBLER,GREEN STRIPE MUG,GREEN STRIPE SPCL,GREEN STRIPE PACK,GREEN STRIPE 1 BASK,GREEN STRIPE 2 BASK,C/O 96 FRZN MOCHA,COLOSSAL MUG,COLSSAL MUG SPCL,COLSSAL MUG PACK,COLSAL MUG 1 BAS,COLSAL MUG 2 BAS,DANCER MUG,DANCER MUG SPECL,DANCER MUG P" & _
        "R MUG 1 BAS,DANCER MUG 2 BAS,FLEUR DE LIS,FLR D LS SPL,FLR D L PACK,FR D LS 1 BK,FR D LS 2 BK,CLEAR TUMBLER,CLEAR TUMBL SPCL,WATER COLOR MUG,WATER COLOR SPCL,WATER COLOR PACK,1 WTR COLOR BSKT,2 WTR COLOR BSKT,C/O 160 HOT,C/O 160 MOCHA,VIBRANT MUG,VIBRANT SPECIAL,VIBRANT PACKAGE,VIBRANT 1 BASK,VIBRANT 2 BASK,LG LOGO MUG,LG LOGO MUG SPCL,LG LOGO PACKAGE,LG LOGO 1 BASK" & _
        "O 2 BASKET,LOGO SHOT GLASS,LOGO TRAVEL MUG,LOGO TVL MUG SPL,C/O 160 ICE,MINI LOGO MUG,MINI WHITE MUG", DataOption:=xlSortNormal

    With ws.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

Open in new window



 I would like to be able to have this sub run when I open the workbook is that possible? Also would there be anyway to Make a script that I can run to run on this spreadsheet. The spreadsheet is recreated everyday and not sure if when it is recreated if it will over right the macro.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Saurabh Singh TeotiaCommented:
In that case what  i will advice is that create a master template from which you define the path and open this workbook at the starting of the macro and run the macro..That will do what you are looking for...
0
 
Fradojr88Author Commented:
After I create the template is there way In the macro i can point to another spread sheet and import the data to the template then run my sort?
0
 
Fradojr88Author Commented:
I have started with the following code but as I further read the example i found  
Sub Mergeworkbooks()
Dim Sheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim SourceRange As Range
Dim DestRange As Range
' Creating the new workbook
Set Sheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
'Folder Path
FolderPath = "C:\Users\XXXXX\Desktop\CDM\"

FileName = ("Counter.pmix")
Set Workbk = Workbooks.Open(FolderPath & FileName)

End Sub

Open in new window


I also noticed when i tried to import the file its importing all the information and not importing it in the proper format.
0
 
Saurabh Singh TeotiaCommented:
When you say it doesn't import in proper format? What happens? What is the problem that you face??
0
 
Fradojr88Author Commented:
It was importing into one column. And not spreading it out this import below worked properly how ever the code is giving a error and I created it by recording the macro see below.

Sub ImportMacro()
'
' Macro2 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Fradosta\Desktop\Counter.pmx.csv", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "Counter.pmx_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub sortdata()
    Dim rng As Range, lrow As Long
    Dim ws As Worksheet

    Set ws = Sheets("Counter.pmx")

    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = ws.Range("a11:i" & lrow)

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("B11:B" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "AU LAIT,SM AU LAIT,MD AU LAIT,LG AU LAIT,BLACK COFFEE,SM BLACK COFFEE,MD BLACK COFFEE,LG BLACK COFFEE,MOCHA,SM MOCHA,MD MOCHA,LG MOCHA,HOT CHOCOLATE,SM HOT CHOCOLATE,MD HOT CHOCOLATE,LG HOT CHOCOLATE,CONTINENTAL,SM CONTINENTAL,MD CONTINENTAL,LG CONTINENTAL,AMERICAN,SM AMERICAN,MD AMERICAN,LG AMERICAN,O.J.,SM O.J.,MD O.J.,LG O.J.,ICED TEA,WHITE MILK,SM WHITE MILK,LG " & _
        "LK,CHOCOLATE MILK,SM CHOCO MILK,LG CHOCO MILK,BOTTLE WATER,ICE WATER,BEIGNETS-1 ORDER,BEIGNETS-2 ORDER,BEIGNETS-3 ORDER,BEIGNETS-4 ORDER,FROZEN AU LAIT,SM FROZ AU LAIT,LG FROZ AU LAIT,FROZEN MOCHA,SM FROZ MOCHA,LG FROZ MOCHA,ADD PUMP CHOCO,SM SODA,MD SODA,LG SODA,COKE,DIET COKE,DR PEPPER,SPRITE,ROOT BEER,FRUIT PUNCH,OTHER DRINK,C/O 96 HOT,C/O 96 MOCHA,C/O 96 ICE,SOD" & _
        "C/O 96 ICE MOCHA,AIRPOT,AIRPOT REFILL,C/O 96 FROZEN,WALKER SPECIAL,REG CAN COFFEE,DECAF CAN COFFEE,FR ROAST CAN COF,BOX MIX,WHT LOGO APRON,LOGO T SHIRT,SOUVENIR MUG,SOUVENIR MUG SPL,SOUVENIR MUG PK,SOUVENIR MUG 1BK,SOUVENIR MUG 2BK,ARCHWAY MUG,ARCHWY MUG SPECL,ARCH MUG PACKAGE,ARCH MUG 1 BASK,ARCH MUG 2 BASK,LOGO MUG,LOGO MUG SPECIAL,LOGO MUG PACKAGE,LOGO MUG 1 BASK" & _
        " 2 BASK,DINER MUG,DINER MUG SPECL,DINER MUG PACK,DINER MUG 1 BASK,DINER MUG 2 BASK,TERVIS MUG,TERVIS 16 oz TUMBLER,TERVIS 24 oz TUMBLER,GREEN STRIPE MUG,GREEN STRIPE SPCL,GREEN STRIPE PACK,GREEN STRIPE 1 BASK,GREEN STRIPE 2 BASK,C/O 96 FRZN MOCHA,COLOSSAL MUG,COLSSAL MUG SPCL,COLSSAL MUG PACK,COLSAL MUG 1 BAS,COLSAL MUG 2 BAS,DANCER MUG,DANCER MUG SPECL,DANCER MUG P" & _
        "R MUG 1 BAS,DANCER MUG 2 BAS,FLEUR DE LIS,FLR D LS SPL,FLR D L PACK,FR D LS 1 BK,FR D LS 2 BK,CLEAR TUMBLER,CLEAR TUMBL SPCL,WATER COLOR MUG,WATER COLOR SPCL,WATER COLOR PACK,1 WTR COLOR BSKT,2 WTR COLOR BSKT,C/O 160 HOT,C/O 160 MOCHA,VIBRANT MUG,VIBRANT SPECIAL,VIBRANT PACKAGE,VIBRANT 1 BASK,VIBRANT 2 BASK,LG LOGO MUG,LG LOGO MUG SPCL,LG LOGO PACKAGE,LG LOGO 1 BASK" & _
        "O 2 BASKET,LOGO SHOT GLASS,LOGO TRAVEL MUG,LOGO TVL MUG SPL,C/O 160 ICE,MINI LOGO MUG,MINI WHITE MUG", DataOption:=xlSortNormal

    With ws.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

                   

Open in new window



When I try and run that macro on the Blank Template(Macro-Enabled) it gives me invalid procedure call or argument.
0
 
Saurabh Singh TeotiaCommented:
I won't do this way ..I will do the earlier way you told.. Then i understand it will be arrange data in 1 column..then on top of it you can apply text to column on the data which will split the data into multiple columns and then you can work with macro on this which will do what you are looking for...
0
 
Fradojr88Author Commented:
Well, it needs to stay in the proper format as this is a system generated report. Generated each day and need it to be as simple as opening the file and then its in the correct order and format once they open the file.
0
 
Saurabh Singh TeotiaCommented:
I understand that..However since it get exported as a CSV and if you open that CSV file in excel programmatically..then in excel in column-A it will club all the data in that column..On which you can use Data-->Text TO Columns which will split the data in multiple columns which is essentially not but the format in which your data is exported ..Again once the macro has run you can save changes as No in your original file that way the original file remain intact in the format and you do all the scrubbing in your macro code only...
0
 
Fradojr88Author Commented:
 Sub ImportMacro()
'
' Macro2 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Fradosta\Desktop\Counter.pmx.csv", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "Counter.pmx_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
]

Open in new window


Ok this import which I used the recording macro function to accomplish pulls the data in on correct format could you give me a example of what Text > Import The columns would need to broken into 9 Columns total.
0
 
Saurabh Singh TeotiaCommented:
Use this code...

Sub getdata()
Dim wb As Workbook, xpath As String
Dim wb1 As Workbook
Set wb = ThisWorkbook

xpath = "C:\Users\Fradosta\Desktop\Counter.pmx.csv"


Set wb1 = Workbooks.Open(xpath)

Dim rng As Range, lrow As Long
    Dim ws As Worksheet

    Set ws = Sheets("CounterBlank.pmx")

    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = ws.Range("a11:i" & lrow)

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("B11:B" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ws.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window


This will open your csv file and do the necessary coding what you are looking for...

Saurabh
0
 
Fradojr88Author Commented:
It is importing the file perfectly but getting a run time error 9 before the sort. Here is the code I dont see what is causing the error?

Sub getdata()
Dim wb As Workbook, xpath As String
Dim wb1 As Workbook
Set wb = ThisWorkbook

xpath = "C:\Users\Fradosta\Desktop\Counter.pmx.csv"


Set wb1 = Workbooks.Open(xpath)

Dim rng As Range, lrow As Long
    Dim ws As Worksheet

    Set ws = Sheets("CounterBlank.pmx")

    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = ws.Range("a11:i" & lrow)

    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("B11:B" & lrow), SortOn:=xlSortOnValues, CustomOrder:= _
        "AU LAIT,SM AU LAIT,MD AU LAIT,LG AU LAIT,BLACK COFFEE,SM BLACK COFFEE,MD BLACK COFFEE,LG BLACK COFFEE,MOCHA,SM MOCHA,MD MOCHA,LG MOCHA,HOT CHOCOLATE,SM HOT CHOCOLATE,MD HOT CHOCOLATE,LG HOT CHOCOLATE,CONTINENTAL,SM CONTINENTAL,MD CONTINENTAL,LG CONTINENTAL,AMERICAN,SM AMERICAN,MD AMERICAN,LG AMERICAN,O.J.,SM O.J.,MD O.J.,LG O.J.,ICED TEA,WHITE MILK,SM WHITE MILK,LG " & _
        "LK,CHOCOLATE MILK,SM CHOCO MILK,LG CHOCO MILK,BOTTLE WATER,ICE WATER,BEIGNETS-1 ORDER,BEIGNETS-2 ORDER,BEIGNETS-3 ORDER,BEIGNETS-4 ORDER,FROZEN AU LAIT,SM FROZ AU LAIT,LG FROZ AU LAIT,FROZEN MOCHA,SM FROZ MOCHA,LG FROZ MOCHA,ADD PUMP CHOCO,SM SODA,MD SODA,LG SODA,COKE,DIET COKE,DR PEPPER,SPRITE,ROOT BEER,FRUIT PUNCH,OTHER DRINK,C/O 96 HOT,C/O 96 MOCHA,C/O 96 ICE,SOD" & _
        "C/O 96 ICE MOCHA,AIRPOT,AIRPOT REFILL,C/O 96 FROZEN,WALKER SPECIAL,REG CAN COFFEE,DECAF CAN COFFEE,FR ROAST CAN COF,BOX MIX,WHT LOGO APRON,LOGO T SHIRT,SOUVENIR MUG,SOUVENIR MUG SPL,SOUVENIR MUG PK,SOUVENIR MUG 1BK,SOUVENIR MUG 2BK,ARCHWAY MUG,ARCHWY MUG SPECL,ARCH MUG PACKAGE,ARCH MUG 1 BASK,ARCH MUG 2 BASK,LOGO MUG,LOGO MUG SPECIAL,LOGO MUG PACKAGE,LOGO MUG 1 BASK" & _
        " 2 BASK,DINER MUG,DINER MUG SPECL,DINER MUG PACK,DINER MUG 1 BASK,DINER MUG 2 BASK,TERVIS MUG,TERVIS 16 oz TUMBLER,TERVIS 24 oz TUMBLER,GREEN STRIPE MUG,GREEN STRIPE SPCL,GREEN STRIPE PACK,GREEN STRIPE 1 BASK,GREEN STRIPE 2 BASK,C/O 96 FRZN MOCHA,COLOSSAL MUG,COLSSAL MUG SPCL,COLSSAL MUG PACK,COLSAL MUG 1 BAS,COLSAL MUG 2 BAS,DANCER MUG,DANCER MUG SPECL,DANCER MUG P" & _
        "R MUG 1 BAS,DANCER MUG 2 BAS,FLEUR DE LIS,FLR D LS SPL,FLR D L PACK,FR D LS 1 BK,FR D LS 2 BK,CLEAR TUMBLER,CLEAR TUMBL SPCL,WATER COLOR MUG,WATER COLOR SPCL,WATER COLOR PACK,1 WTR COLOR BSKT,2 WTR COLOR BSKT,C/O 160 HOT,C/O 160 MOCHA,VIBRANT MUG,VIBRANT SPECIAL,VIBRANT PACKAGE,VIBRANT 1 BASK,VIBRANT 2 BASK,LG LOGO MUG,LG LOGO MUG SPCL,LG LOGO PACKAGE,LG LOGO 1 BASK" & _
        "O 2 BASKET,LOGO SHOT GLASS,LOGO TRAVEL MUG,LOGO TVL MUG SPL,C/O 160 ICE,MINI LOGO MUG,MINI WHITE MUG", DataOption:=xlSortNormal, DataOption:=xlSortNormal

    With ws.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

0
 
Saurabh Singh TeotiaCommented:
Any particular reason why you are giving a custom order rather then giving ascending or descending order as in custom order if any data value which you gave and it's not their like in this case it will give you an error...
0
 
Fradojr88Author Commented:
It is a particular order that the spreadsheet needs to be sorted in. I also got the same error when I ran it with out the custom list. Am i Just pasting this code into the General section of  a spreadsheet? I am wondering if im putting it in the right place?
0
 
Saurabh Singh TeotiaCommented:
Can you give me a copy of your file on which you are running this code or is the same copy what you uploaded here??
0
 
Fradojr88Author Commented:
Here are the two files Book1 is the blank. With the code and Counter has the data that needs to be imported and sorted. with as little interaction as possible from the user. Once I get this part right I will be adding it to a batch file that will email the sorted reports out. THere will be times when not everything in the custom list will be there as this is a sales report and if the product isn't sold it wont be on the report.
Book1.xlsm
Counter.pmx.csv
0
 
Saurabh Singh TeotiaCommented:
Okay i fixed that.. and you need to change this line...

 Set ws = Sheets("CounterBlank.pmx")

to this..

 Set ws = Sheets("Counter.pmx")

Now the really problem is your custom sort because in your custom sort you are considering the values which are not even their..plus in additional you are trying to apply the sort only on partial values..as it doesn't take care about rest of the values which are their in the data ..which is what you want to do of them and as a result it gives you an error on your sorting now...
0
 
Fradojr88Author Commented:
I can make the report show the data even if there are none sold. If that would help.
0
 
Saurabh Singh TeotiaCommented:
Quick question..Which version of excel you are using as i need to make sure i write that code in that version of excel..

And you can't do the custom sorting like the way of values you want to do since max char length in custom list it supports is 255 and you have more values in it so it won't work the way what you are doing..alternative way what i can think about is doing a vlookup on the worksheet of the sorting order from some reference point and then doing a sort on that column in ascending which will do what you are looking for..Let me know if you are fine with that approach...
0
 
Fradojr88Author Commented:
I am currently working on the custom list as some terms dont match the spread sheet. I will let you know how it goes thanks again for all your help. what could I add to the code to make it save it after the sort.
0
 
Saurabh Singh TeotiaCommented:
Did you try the revised version of the code which i gave to you as that does the custom list what you are looking for...

Also to save the workbook and close you can add this line..This will save the file in excel format on your desktop only with updated in it..

       wb1.SaveAs Filename:="C:\Users\Fradosta\Desktop\Counter.pmx (Updated).xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
       wb1.Close (True)

Open in new window

0
 
Fradojr88Author Commented:
I am using excel 2010.  And if I Import the custom list into the system it allows for more then 255. I have the sorting working correctly if I run it as a macro before I reorganized the list. I am working on reorganizing the list with matching terms.
0
 
Fradojr88Author Commented:
I am looking at the revised now as I just noticed this
0
 
Fradojr88Author Commented:
The new code works and sorts it properly. Is there anyway that reference list can be more then the 138 items? Am I simply able to add to that list? Also is there anyway to where it can run the macro with out having to hit run macro?
0
 
Saurabh Singh TeotiaCommented:
You can add more to the custom list and it will take them automatically as the macro is adaptive in nature.. In additional if you want to run the macro code on its own..

Just move the code in thisworkbook module and in workbook open...that way as soon you open the workbook it will run the macro code on its own..

Saurabh...
0
 
Fradojr88Author Commented:
We are so close!!! The macro when you run it as macro works perfectly. How can i get it to Overwrite a save file as well? Here is what I have. The macro works fine when you run it on open it gets hung up with error 9 and on the line
Set ws = Sheets("Counter.pmx")

Open in new window

Book1--1-.xlsm
0
 
Saurabh Singh TeotiaCommented:
Change this to...

Set ws = ActiveSheet
0
 
Fradojr88Author Commented:
That worked! thanks so much. How Can I overwrite a existing file to where it does not prompt?
0
 
Saurabh Singh TeotiaCommented:
Write at the top of the code add

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Open in new window

0
 
Fradojr88Author Commented:
Anything that would could have changed to make it stopp sorting? Everything is working except the saved document is not sorted? It seems that the change
Set ws = Sheets("Counter.pmx")

Open in new window

0
 
Saurabh Singh TeotiaCommented:
I don't think this should create a difference as you are setting it to active sheet or you can set it to the worksheet name which you observe when you open the workbook as this should do what you are looking for as tested in the code..
0
 
Fradojr88Author Commented:
Its odd it sorts and saves correctly as the macro. When you run the same code in the open parameters its not sorted. Could the difference be that the the opening code does not sort.
Private Sub Workbook_Open()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

    Dim wb As Workbook, xpath As String
    Dim wb1 As Workbook
    Set wb = ThisWorkbook

    xpath = "C:\Users\Fradosta\Desktop\Counter.pmx.csv"
    Dim r As Range, cell As Range
    Dim lr As Long, ws1 As Worksheet, z As Long

    Set ws1 = Sheets("Sheet1")
    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set r = ws1.Range("B1:B" & lr)
    z = Application.WorksheetFunction.Max(r)

    Set r = ws1.Range("A1:B" & lr)


    Set wb1 = Workbooks.Open(xpath)

    Dim rng As Range, lrow As Long
    Dim ws As Worksheet

   
    
    lrow = ws.Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = ws.Range("a11:j" & lrow)

    ws.Range("J11:J" & lrow).Formula = "=IFERROR(VLOOKUP(B11,'[" & ThisWorkbook.Name & "]Sheet1'!$A:$B,2,0) ," & z + 1 & ")"
    ws.Range("J11:J" & lrow).Value = ws.Range("J11:J" & lrow).Value
    ws.Sort.SortFields.Clear

    ws.Sort.SortFields.Add Key:=Range("J11:J" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ws.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("J11:J" & lrow).Value = ""
Rem wb1.SaveAs Filename:="C:\Users\Aloha\Desktop\Reports\CounterSorted.pmx.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Rem   wb1.Close (True)
       Rem wb.Close (True)


End Sub

Open in new window


Could have something to do with that I removed the sub getdata() as it would not let me save it under open.
0
 
Saurabh Singh TeotiaCommented:
No, You are missing an important line in the code arround line-27

which is

set ws=activesheet

Or you can use this entire code..

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

    Dim wb As Workbook, xpath As String
    Dim wb1 As Workbook
    Set wb = ThisWorkbook

    xpath = "C:\Users\Fradosta\Desktop\Counter.pmx.csv"
    Dim r As Range, cell As Range
    Dim lr As Long, ws1 As Worksheet, z As Long

    Set ws1 = Sheets("Sheet1")
    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set r = ws1.Range("B1:B" & lr)
    z = Application.WorksheetFunction.Max(r)

    Set r = ws1.Range("A1:B" & lr)


    Set wb1 = Workbooks.Open(xpath)

    Dim rng As Range, lrow As Long
    Dim ws As Worksheet

   
    
    lrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row - 6

    Set rng = Range("a11:j" & lrow)

    Range("J11:J" & lrow).Formula = "=IFERROR(VLOOKUP(B11,'[" & ThisWorkbook.Name & "]Sheet1'!$A:$B,2,0) ," & z + 1 & ")"
    Range("J11:J" & lrow).Value = Range("J11:J" & lrow).Value
    ActiveSheet.Sort.SortFields.Clear

    ActiveSheet.Sort.SortFields.Add Key:=Range("J11:J" & lrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveSheet.Sort
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("J11:J" & lrow).Value = ""
Rem wb1.SaveAs Filename:="C:\Users\Aloha\Desktop\Reports\CounterSorted.pmx.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Rem   wb1.Close (True)
       Rem wb.Close (True)


End Sub

Open in new window

0
 
Fradojr88Author Commented:
Yea I noticed that line was missing and adding it back, but its still not sorting. The only other difference between the macro code and the open code is the sub header and the macro sorts and the open does not its kind of strange.

How could I add the sub into the Workbook(Open)
0
 
Saurabh Singh TeotiaCommented:
I don't think this should create a problem..

Private Sub Workbook_Open()
getdata
End Sub

Open in new window


And move getdata macro in the new module of your workbook and let's see whether that resolves the problem which you are facing...
0
 
Fradojr88Author Commented:
Oddly enough that appears to have worked. I will un rem the save & close code and let you know the results.

Thanks again.
0
 
Fradojr88Author Commented:
We are 100% working! Thanks Again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.