Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort and copy selection to other worksheet
I have the following: -BUT it doesn't sort Column R
' determine total range for sorting Dim lr As Long, lc As Long '(in declarations) lc = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row Cells.Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(1, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(1, 1), Cells(lr, lc)) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With After this sorts Column R, I then need to copy values of Fields C,D,E for the rows where value in R = #N/A to another WorkSheet. Can you help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort and copy selection to other worksheet
Hi Helmut
Based on what you supplied, this code might do the job: Assumptions : - sheet1 contains a number of columns A-R - sheet2 is the destination sheet - All data starts at row 1, column A <-- snip start -- 'I have the following: -BUT it doesn't sort Column R ' determine total range for sorting Dim lr As Long, lc As Long '(in declarations) Sub testme() lc = sheet1.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column lr = sheet1.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row Sheet1.Range("A1").Sort _ Key1:=Range(Cells(1, lc), Cells(lr, lc)), _ Order1:=xlAscending, _ DataOption1:=xlSortNormal, _ Header:=xlGuess Dim eRng As Range Set eRng = Range("A1").SpecialCells(xlCellTypeConstants, xlErrors) Range(Cells(eRng.Row, 3), Cells(eRng.Row + eRng.Rows.Count - 1, 5)).Select Selection.Copy Destination:=Worksheets("Sheet2").Range("E5") End Sub 'After this sorts Column R, I then need to copy values of Fields C,D,E for 'the rows where value in R = #N/A to another WorkSheet. 'Can you help? <-- snip end -- Cheers David "Helmut" wrote: I have the following: -BUT it doesn't sort Column R ' determine total range for sorting Dim lr As Long, lc As Long '(in declarations) lc = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column lr = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row Cells.Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add _ Key:=Range(Cells(1, 1), Cells(lr, 1)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveSheet.Sort .SetRange Range(Cells(1, 1), Cells(lr, lc)) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With After this sorts Column R, I then need to copy values of Fields C,D,E for the rows where value in R = #N/A to another WorkSheet. Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort by even and odd digits and copy to separate worksheet | Excel Worksheet Functions | |||
Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
copy Data selection from one workshhet into an existing worksheet | Excel Worksheet Functions | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming |