LinkBack URL
About LinkBacksI 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?
Register To Reply
Bookmarks