ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sorting in Ascending Order (https://www.excelbanter.com/excel-programming/407962-sorting-ascending-order.html)

Diana Bowe
Sorting in Ascending Order
 
Attached is the visual basic coding for the macro which creates a journal
entry for upload. I need to sort this in ascending order because descending
order is causing an intercompany nightmare (as we do not consolidate the
higher numbered companies). How can I change the code to sort in ascending
order?

Private Sub CommandButton1_Click()

Set oldsheet = ActiveSheet

Sheets("stats").Select

filenamer = Range("a25")

importrow = Range("e4")

importname = Range("f4")

newdata = Range("b2")

Dim path As String

path = Range("b1")

Application.Calculation = xlCalculationManual

Sheets("data").Select

' Deletes old date for a reimport

Range("a2").Offset(0, importrow).Select

x = ActiveCell.Address

Range(ActiveCell, Range(x).End(xlDown).Offset(0, 2)).ClearContents

' Opens download for import

Workbooks.Open Filename:=path

Range("a1:c1", Range("a1").End(xlDown)).Copy

Windows(filenamer).Activate

Sheets("data").Select

' Pastes data in the proper place

Range("a2").Offset(0, importrow).PasteSpecial

' Adds the extra GL codes (so vlookup's do not equal N/A)

Sheets("stats").Select

Range("l4", Range("l4").End(xlDown)).Copy

Sheets("data").Select

Range("a2").Offset(0, importrow).End(xlDown).Offset(1, 0).PasteSpecial

' Names the new range

Range("a2").Offset(0, importrow).Activate

x = ActiveCell.Address

Range(ActiveCell, Range(x).End(xlDown).Offset(0, 2)).Name = importname

ActiveCell.End(xlUp).Offset(1, 0).Select

Do While ActiveCell.Offset(0, 2) 1

Dim i, j

i = ActiveCell

j = RTrim(i)

ActiveCell = j

ActiveCell.Offset(1, 0).Activate

Loop

ActiveCell.Offset(-1, 0).Select

Selection.End(xlUp).Select

Windows(newdata).Close

Application.Calculation = xlCalculationAutomatic

Calculate

oldsheet.Activate

End Sub

Private Sub CommandButton2_Click()

Set oldsheet = ActiveSheet

Application.Calculation = xlCalculationAutomatic

Calculate

Sheets("Stats").Select

exportname = Range("h1")

Import# = Range("g3")

If Range("a22") = 1 Then

Sheets("TOTAL").Select

Columns(Import#).Copy

Columns("c:C").PasteSpecial Paste:=xlValues

'The end of the following range should be the last row on the adj. page
with a g/l no.

Range("A9:C1000").Copy

Sheets("sort").Activate

Range("a1").PasteSpecial Paste:=xlValues

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Count = Range(("a1"), Range("a1").End(xlDown)).Count + 1

Count2 = Count - 1

Range("a1").End(xlDown).Offset(1, 0).Select

Range(ActiveCell, Range("c2500")).ClearContents

'The beginning of the following range should start with the 1 row after
the last row on the sort pg.

Columns("A:A").Copy

Sheets("UPLOAD").Select

Cells.EntireRow.Hidden = False

Cells([Count], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

Range("D1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("B:B").Copy

Sheets("UPLOAD").Select

Range("B1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("C:C").Copy

Sheets("UPLOAD").Select

Range("E1").PasteSpecial Paste:=xlValues

bbb = Range(Cells([Count], 1), Cells(65536, 12)).ClearContents

Range("a1").Copy

Range(Cells([Count2], 1), Cells(2, 1)).PasteSpecial Paste:=xlValues

Range("c1").Copy

Range(Cells([Count2], 3), Cells(2, 3)).PasteSpecial Paste:=xlValues

Range("f1:l1").Copy

Range(Cells([Count2], 6), Cells(2, 6)).PasteSpecial Paste:=xlValues

'The end of the following range should equal the last visual row number
on the upload page.

Range(("a1"), Range("a1").End(xlDown).Offset(0, 11)).Copy

Workbooks.Open Filename:="J:\DOR\DIJ\Upload.xls"

Cells.EntireRow.Hidden = False

Range("a1").PasteSpecial Paste:=xlValues

Cells([Count2], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

ActiveWorkbook.SaveAs Filename:=exportname, _

FileFormat:=xlTextPrinter, CreateBackup:=False

ActiveWorkbook.Close

Else

Sheets("ADJUST").Select

Columns(Import#).Copy

Columns("c:C").PasteSpecial Paste:=xlValues

'The end of the following range should be the last row on the adj. page
with a g/l no.

Range("A9:C1000").Copy

Sheets("sort").Activate

Range("a1").PasteSpecial Paste:=xlValues

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Count = Range(("a1"), Range("a1").End(xlDown)).Count + 1

Count2 = Count - 1

Range("a1").End(xlDown).Offset(1, 0).Select

Range(ActiveCell, Range("c2500")).ClearContents

'The beginning of the following range should start with the 1 row after
the last row on the sort pg.

Columns("A:A").Copy

Sheets("UPLOAD").Select

Cells.EntireRow.Hidden = False

Cells([Count], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

Range("D1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("B:B").Copy

Sheets("UPLOAD").Select

Range("B1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("C:C").Copy

Sheets("UPLOAD").Select

Range("E1").PasteSpecial Paste:=xlValues

bbb = Range(Cells([Count], 1), Cells(65536, 12)).ClearContents

Range("a1").Copy

Range(Cells([Count2], 1), Cells(2, 1)).PasteSpecial Paste:=xlValues

Range("c1").Copy

Range(Cells([Count2], 3), Cells(2, 3)).PasteSpecial Paste:=xlValues

Range("f1:l1").Copy

Range(Cells([Count2], 6), Cells(2, 6)).PasteSpecial Paste:=xlValues

'The end of the following range should equal the last visual row number
on the upload page.

Range(("a1"), Range("a1").End(xlDown).Offset(0, 11)).Copy

Workbooks.Open Filename:="J:\DIJ\Upload.xls"

Cells.EntireRow.Hidden = False

Range("a1").PasteSpecial Paste:=xlValues

Cells([Count2], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

ActiveWorkbook.SaveAs Filename:=exportname, _

FileFormat:=xlTextPrinter, CreateBackup:=False

ActiveWorkbook.Close

End If

oldsheet.Activate

End Sub



Private Sub CommandButton3_Click()

Calculate

Set oldsheet = ActiveSheet

Sheets("stats").Select

f = Range("d4") - 1

Sheets("total").Select

'selects start of range

Range("d7").Select

Set SumRange = Range(ActiveCell, ActiveCell.Offset(0, f))

Range("c7").Formula = "=SUM(" & SumRange.Address(False, False) & ")"

Range("c7:c617").FillDown

oldsheet.Activate

End Sub



Private Sub CommandButton4_Click()

Dim Msg, Style, Title, Response, MyString

Msg = "This will clear all of the adjustemnts. Do you want to
continue ?" ' Define message.

Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.

Title = "TIP CLEARER"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

Sheets("income").Select

Cells.EntireColumn.Hidden = False

Columns("gi:gj").Select

Selection.Copy

Columns("F:F").Select

ActiveSheet.Paste

Columns("L:L").Select

ActiveSheet.Paste

Columns("R:R").Select

ActiveSheet.Paste

Columns("X:X").Select

ActiveSheet.Paste

Columns("AD:AD").Select

ActiveSheet.Paste

Columns("AJ:AJ").Select

ActiveSheet.Paste

Columns("AP:AP").Select

ActiveSheet.Paste

Columns("AV:AV").Select

ActiveSheet.Paste

Columns("BB:BB").Select

ActiveSheet.Paste

Columns("BH:BH").Select

ActiveSheet.Paste

Columns("BN:BN").Select

ActiveSheet.Paste

Columns("BT:BT").Select

ActiveSheet.Paste

Columns("BZ:BZ").Select

ActiveSheet.Paste

Columns("CF:CF").Select

ActiveSheet.Paste

Columns("CL:CL").Select

ActiveSheet.Paste

Columns("CR:CR").Select

ActiveSheet.Paste

Columns("CX:CX").Select

ActiveSheet.Paste

Columns("DD:DD").Select

ActiveSheet.Paste

Columns("DJ:DJ").Select

ActiveSheet.Paste

Columns("DP:DP").Select

ActiveSheet.Paste

Columns("DV:DV").Select

ActiveSheet.Paste

Columns("EB:EB").Select

ActiveSheet.Paste

Columns("EH:EH").Select

ActiveSheet.Paste

Columns("EN:EN").Select

ActiveSheet.Paste

Columns("ET:ET").Select

ActiveSheet.Paste

Columns("EZ:EZ").Select

ActiveSheet.Paste

Columns("FF:FF").Select

ActiveSheet.Paste

Columns("FL:FL").Select

ActiveSheet.Paste

Columns("FR:FR").Select

ActiveSheet.Paste

Columns("FX:FX").Select

ActiveSheet.Paste

Columns("GD:GD").Select

ActiveSheet.Paste

Sheets("DATA").Select

Rows("2:1499").Select

Application.CutCopyMode = False

Selection.ClearContents

Else

Exit Sub

End If

End Sub



Private Sub ListBox1_Click()



End Sub

Bernie Deitrick
Sorting in Ascending Order
 
Diana,

Change xlDescending to xlAscending...

HTH,
Bernie
MS Excel MVP


"Diana Bowe" wrote in message ...
Attached is the visual basic coding for the macro which creates a journal entry for upload. I need
to sort this in ascending order because descending order is causing an intercompany nightmare (as
we do not consolidate the higher numbered companies). How can I change the code to sort in
ascending order?

Private Sub CommandButton1_Click()

Set oldsheet = ActiveSheet

Sheets("stats").Select

filenamer = Range("a25")

importrow = Range("e4")

importname = Range("f4")

newdata = Range("b2")

Dim path As String

path = Range("b1")

Application.Calculation = xlCalculationManual

Sheets("data").Select

' Deletes old date for a reimport

Range("a2").Offset(0, importrow).Select

x = ActiveCell.Address

Range(ActiveCell, Range(x).End(xlDown).Offset(0, 2)).ClearContents

' Opens download for import

Workbooks.Open Filename:=path

Range("a1:c1", Range("a1").End(xlDown)).Copy

Windows(filenamer).Activate

Sheets("data").Select

' Pastes data in the proper place

Range("a2").Offset(0, importrow).PasteSpecial

' Adds the extra GL codes (so vlookup's do not equal N/A)

Sheets("stats").Select

Range("l4", Range("l4").End(xlDown)).Copy

Sheets("data").Select

Range("a2").Offset(0, importrow).End(xlDown).Offset(1, 0).PasteSpecial

' Names the new range

Range("a2").Offset(0, importrow).Activate

x = ActiveCell.Address

Range(ActiveCell, Range(x).End(xlDown).Offset(0, 2)).Name = importname

ActiveCell.End(xlUp).Offset(1, 0).Select

Do While ActiveCell.Offset(0, 2) 1

Dim i, j

i = ActiveCell

j = RTrim(i)

ActiveCell = j

ActiveCell.Offset(1, 0).Activate

Loop

ActiveCell.Offset(-1, 0).Select

Selection.End(xlUp).Select

Windows(newdata).Close

Application.Calculation = xlCalculationAutomatic

Calculate

oldsheet.Activate

End Sub

Private Sub CommandButton2_Click()

Set oldsheet = ActiveSheet

Application.Calculation = xlCalculationAutomatic

Calculate

Sheets("Stats").Select

exportname = Range("h1")

Import# = Range("g3")

If Range("a22") = 1 Then

Sheets("TOTAL").Select

Columns(Import#).Copy

Columns("c:C").PasteSpecial Paste:=xlValues

'The end of the following range should be the last row on the adj. page with a g/l no.

Range("A9:C1000").Copy

Sheets("sort").Activate

Range("a1").PasteSpecial Paste:=xlValues

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Count = Range(("a1"), Range("a1").End(xlDown)).Count + 1

Count2 = Count - 1

Range("a1").End(xlDown).Offset(1, 0).Select

Range(ActiveCell, Range("c2500")).ClearContents

'The beginning of the following range should start with the 1 row after the last row on the
sort pg.

Columns("A:A").Copy

Sheets("UPLOAD").Select

Cells.EntireRow.Hidden = False

Cells([Count], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

Range("D1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("B:B").Copy

Sheets("UPLOAD").Select

Range("B1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("C:C").Copy

Sheets("UPLOAD").Select

Range("E1").PasteSpecial Paste:=xlValues

bbb = Range(Cells([Count], 1), Cells(65536, 12)).ClearContents

Range("a1").Copy

Range(Cells([Count2], 1), Cells(2, 1)).PasteSpecial Paste:=xlValues

Range("c1").Copy

Range(Cells([Count2], 3), Cells(2, 3)).PasteSpecial Paste:=xlValues

Range("f1:l1").Copy

Range(Cells([Count2], 6), Cells(2, 6)).PasteSpecial Paste:=xlValues

'The end of the following range should equal the last visual row number on the upload page.

Range(("a1"), Range("a1").End(xlDown).Offset(0, 11)).Copy

Workbooks.Open Filename:="J:\DOR\DIJ\Upload.xls"

Cells.EntireRow.Hidden = False

Range("a1").PasteSpecial Paste:=xlValues

Cells([Count2], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

ActiveWorkbook.SaveAs Filename:=exportname, _

FileFormat:=xlTextPrinter, CreateBackup:=False

ActiveWorkbook.Close

Else

Sheets("ADJUST").Select

Columns(Import#).Copy

Columns("c:C").PasteSpecial Paste:=xlValues

'The end of the following range should be the last row on the adj. page with a g/l no.

Range("A9:C1000").Copy

Sheets("sort").Activate

Range("a1").PasteSpecial Paste:=xlValues

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Count = Range(("a1"), Range("a1").End(xlDown)).Count + 1

Count2 = Count - 1

Range("a1").End(xlDown).Offset(1, 0).Select

Range(ActiveCell, Range("c2500")).ClearContents

'The beginning of the following range should start with the 1 row after the last row on the
sort pg.

Columns("A:A").Copy

Sheets("UPLOAD").Select

Cells.EntireRow.Hidden = False

Cells([Count], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

Range("D1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("B:B").Copy

Sheets("UPLOAD").Select

Range("B1").PasteSpecial Paste:=xlValues

Sheets("SORT").Select

Columns("C:C").Copy

Sheets("UPLOAD").Select

Range("E1").PasteSpecial Paste:=xlValues

bbb = Range(Cells([Count], 1), Cells(65536, 12)).ClearContents

Range("a1").Copy

Range(Cells([Count2], 1), Cells(2, 1)).PasteSpecial Paste:=xlValues

Range("c1").Copy

Range(Cells([Count2], 3), Cells(2, 3)).PasteSpecial Paste:=xlValues

Range("f1:l1").Copy

Range(Cells([Count2], 6), Cells(2, 6)).PasteSpecial Paste:=xlValues

'The end of the following range should equal the last visual row number on the upload page.

Range(("a1"), Range("a1").End(xlDown).Offset(0, 11)).Copy

Workbooks.Open Filename:="J:\DIJ\Upload.xls"

Cells.EntireRow.Hidden = False

Range("a1").PasteSpecial Paste:=xlValues

Cells([Count2], [1]).Select

Range(ActiveCell, Range("a65536")).EntireRow.Hidden = True

ActiveWorkbook.SaveAs Filename:=exportname, _

FileFormat:=xlTextPrinter, CreateBackup:=False

ActiveWorkbook.Close

End If

oldsheet.Activate

End Sub



Private Sub CommandButton3_Click()

Calculate

Set oldsheet = ActiveSheet

Sheets("stats").Select

f = Range("d4") - 1

Sheets("total").Select

'selects start of range

Range("d7").Select

Set SumRange = Range(ActiveCell, ActiveCell.Offset(0, f))

Range("c7").Formula = "=SUM(" & SumRange.Address(False, False) & ")"

Range("c7:c617").FillDown

oldsheet.Activate

End Sub



Private Sub CommandButton4_Click()

Dim Msg, Style, Title, Response, MyString

Msg = "This will clear all of the adjustemnts. Do you want to continue ?" ' Define
message.

Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.

Title = "TIP CLEARER"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then

Sheets("income").Select

Cells.EntireColumn.Hidden = False

Columns("gi:gj").Select

Selection.Copy

Columns("F:F").Select

ActiveSheet.Paste

Columns("L:L").Select

ActiveSheet.Paste

Columns("R:R").Select

ActiveSheet.Paste

Columns("X:X").Select

ActiveSheet.Paste

Columns("AD:AD").Select

ActiveSheet.Paste

Columns("AJ:AJ").Select

ActiveSheet.Paste

Columns("AP:AP").Select

ActiveSheet.Paste

Columns("AV:AV").Select

ActiveSheet.Paste

Columns("BB:BB").Select

ActiveSheet.Paste

Columns("BH:BH").Select

ActiveSheet.Paste

Columns("BN:BN").Select

ActiveSheet.Paste

Columns("BT:BT").Select

ActiveSheet.Paste

Columns("BZ:BZ").Select

ActiveSheet.Paste

Columns("CF:CF").Select

ActiveSheet.Paste

Columns("CL:CL").Select

ActiveSheet.Paste

Columns("CR:CR").Select

ActiveSheet.Paste

Columns("CX:CX").Select

ActiveSheet.Paste

Columns("DD:DD").Select

ActiveSheet.Paste

Columns("DJ:DJ").Select

ActiveSheet.Paste

Columns("DP:DP").Select

ActiveSheet.Paste

Columns("DV:DV").Select

ActiveSheet.Paste

Columns("EB:EB").Select

ActiveSheet.Paste

Columns("EH:EH").Select

ActiveSheet.Paste

Columns("EN:EN").Select

ActiveSheet.Paste

Columns("ET:ET").Select

ActiveSheet.Paste

Columns("EZ:EZ").Select

ActiveSheet.Paste

Columns("FF:FF").Select

ActiveSheet.Paste

Columns("FL:FL").Select

ActiveSheet.Paste

Columns("FR:FR").Select

ActiveSheet.Paste

Columns("FX:FX").Select

ActiveSheet.Paste

Columns("GD:GD").Select

ActiveSheet.Paste

Sheets("DATA").Select

Rows("2:1499").Select

Application.CutCopyMode = False

Selection.ClearContents

Else

Exit Sub

End If

End Sub



Private Sub ListBox1_Click()



End Sub

Jim Cone
Sorting in Ascending Order
 

On the Edit menu in the VBE choose "Replace" and...
Replace all instances of xlDescending with xlAscending
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Diana Bowe"

wrote in message
Attached is the visual basic coding for the macro which creates a journal
entry for upload. I need to sort this in ascending order because descending
order is causing an intercompany nightmare (as we do not consolidate the
higher numbered companies). How can I change the code to sort in ascending
order?
-snip-


All times are GMT +1. The time now is 01:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com