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