Andrew,

In your Do loop, rngFirstCell is not re-assigned to any cell and is
therefore always pointing to A15 and causes an infinte loop. By the time you
reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps,
you meant to use rngCurrentCell? i.e.,

Do While Not IsEmpty(rngCurrentCell)



--
Hope that helps.

Vergel Adriano


"Andrew Glennie" wrote:

Hi All,

Here is the code. I am referencing 2 different columns because the column
which is doing the selection is sometime blank so the loop needs to reference
a column with data in every row.

The line the error message appears on is:

Set rngNextCell = rngCurrentCell.Offset(1, 0)

Sub Select_Level_4()

strColumnRange = "M15"
str1stColumnRange = "A15"

Sheets("Level 4 TBA").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Clear
Range("A1").Select

Sheets("Staff Training").Activate

Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange)
Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange)
Do While Not IsEmpty(rngFirstCell)
Set rngNextCell = rngCurrentCell.Offset(1, 0)
If rngCurrentCell.Value = "TBA" Then
rngCurrentCell.EntireRow.Copy
Sheets("Level 4 TBA").Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial xlPasteValues
End If
Set rngCurrentCell = rngNextCell
Loop
End Sub

TIA

Andrew