Sorting by two column and bringing third along.

[Guest]

I am using a worksheet to enter data, this code copies and resorts by the
second column and populated a second worksheet. and works great (thank you
to the kind soul who gave this to me BTW)
I'm trying to modify it to bring a third column along for the ride. I don' t
need to sort by the third column, just copy it into the second worksheet
into the same column it belongs in.

I added all references to "C" in the code below but I can't figure out how
to simply modify this to include the third column. It looks to me like the
range and DSTCol are beyond my abilities at present.
I "Watched" the Vars for Range in the debugger but got more of a property
sheet than a range of cells. So I got lost.


Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 1))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 1))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range(.Cells(1, DstCol), .Cells(LastRow, DstCol + 1))
End With

End Sub

Help?

 

[Tom Ogilvy]

This code is pretty verbose, but since you are happy with it:

Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
' change 1 to 2
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 2))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
' change 1 to 2
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 2))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range("A2")
End With

End Sub

 

[Guest]

Thank you, I'll try that first thing tomorrow.
What does " End(xlUp) " do here?
I would welcome a less cumbersome alternative, maybe I can follow it's flow
and understand it instead of just pasting it in.

 

[Tom Ogilvy]

go to row 65536 and select A65536. Hit the End Key, then the Up Arrow.

That is what is means.

 

[Guest]

Oh... Cool!
Thanks again for the assist

go to row 65536 and select A65536. Hit the End Key, then the Up Arrow.

That is what is means.