Sorting in Ascending Order

D

Diana Bowe

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
 
B

Bernie Deitrick

Diana,

Change xlDescending to xlAscending...

HTH,
Bernie
MS Excel MVP


Diana Bowe said:
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
Click to expand...
 
J

Jim Cone

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"
<[email protected]>
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-
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with a VBA subroutine 0
Copy columns and paste with blank column between 5
Help me5 1
Object variable or with Block variable not set 9
Help merging two VBA codes 2
works in 2007 but not in 2010 0
Copy row info but not in normal column sequence 6
End(xlDown) Issue 1

Top