VBA to sort worksheet

[depcdivr]

This one has me stumped. I am trying to sort a data range in a spreadsheet in descending order, extract a few names and then resort on a different column and repeat. I rand the program the first time and it worked. Now it errors out on the sort line saying "the sort reference s not valid. Make sure that it's within the data you want to sort and the first sort box isn't the same or blank."

Here is the code i'm using.

                Set wb = Workbooks.Open(strx & "\" & MyFile)
                Set ws = wb.Sheets("Rep Summary")
                Set wsPOS = wb.Sheets("POS Summary")

                    wsPOS.Sort.SortFields.Clear
                    irow = wsPOS.Cells(Rows.Count, 1).End(xlUp).Row
                    

                    wsPOS.Range(wsPOS.Cells(8, 1), wsPOS.Cells(irow - 1, 161)).Sort Key1:=Range("BC8"), Header:=xlNo, order1:=xlDescending
                    ws.Cells(3, 9) = wsPOS.Cells(8, 1)
                    ws.Cells(4, 9) = wsPOS.Cells(9, 1)
                    ws.Cells(5, 9) = wsPOS.Cells(10, 1)
                                
                    wsPOS.Range(wsPOS.Cells(8, 1), wsPOS.Cells(irow - 1, 161)).Sort Key1:=Range("DE8"), Header:=xlNo, order1:=xlDescending
                    ws.Cells(3, 10) = wsPOS.Cells(8, 1)
                    ws.Cells(4, 10) = wsPOS.Cells(9, 1)
                    ws.Cells(5, 10) = wsPOS.Cells(10, 1)

 

[Skyybot]

I'm not exactly sure of what the problem is, but if you shared the code where it errors out, then try this. ( I see redundant assignments)

Set wb = Workbooks.Open(strx & "\" & MyFile)
                Set ws = wb.Sheets("Rep Summary")
                Set wsPOS = wb.Sheets("POS Summary")

                    wsPOS.Sort.SortFields.Clear
                    irow = wsPOS.Cells(Rows.Count, 1).End(xlUp).Row
                    

                    wsPOS.Range(Cells(8, 1), Cells(irow - 1, 161)).Sort Key1:=Range("BC8"), Header:=xlNo, order1:=xlDescending
                    ws.Cells(3, 9) = wsPOS.Cells(8, 1)
                    ws.Cells(4, 9) = wsPOS.Cells(9, 1)
                    ws.Cells(5, 9) = wsPOS.Cells(10, 1)
                                
                    wsPOS.Range(Cells(8, 1), Cells(irow - 1, 161)).Sort Key1:=Range("DE8"), Header:=xlNo, order1:=xlDescending
                    ws.Cells(3, 10) = wsPOS.Cells(8, 1)
                    ws.Cells(4, 10) = wsPOS.Cells(9, 1)
                    ws.Cells(5, 10) = wsPOS.Cells(10, 1)

 

[Fluff]

You need to qualify the Key ranges as well, like

                Set wb = Workbooks.Open(strx & "\" & myFile)
                Set ws = wb.Sheets("Rep Summary")
                Set wsPos = wb.Sheets("POS Summary")

                    wsPos.Sort.SortFields.Clear
                    iRow = wsPos.Cells(Rows.Count, 1).End(xlUp).Row
                    

                    wsPos.Range(wsPos.Cells(8, 1), wsPos.Cells(iRow - 1, 161)).Sort Key1:=wsPos.Range("BC8"), Header:=xlNo, order1:=xlDescending
                    ws.Cells(3, 9) = wsPos.Cells(8, 1)
                    ws.Cells(4, 9) = wsPos.Cells(9, 1)
                    ws.Cells(5, 9) = wsPos.Cells(10, 1)
                                
                    wsPos.Range(wsPos.Cells(8, 1), wsPos.Cells(iRow - 1, 161)).Sort Key1:=wsPos.Range("DE8"), Header:=xlNo, order1:=xlDescending
                    ws.Cells(3, 10) = wsPos.Cells(8, 1)
                    ws.Cells(4, 10) = wsPos.Cells(9, 1)
                    ws.Cells(5, 10) = wsPos.Cells(10, 1)