90% there with code, small problem!!

[jjsteele]

Can someone please help me with this code, it almost works!!
What I am trying to do is allow a user to select a cell in colunm A the macro would then copy the row (A:I columns only) to another sheet at a certain location on the sheet depending on its value in column D of the original sheet.

Sub ThyroidClinicSelect()

Dim LastRow As Object

Dim rw As Long



 If ActiveCell.Column = 1 Then
 
 rw = ActiveCell.Row
 


Set LastRow = Sheets("ThyroidTemplate").Range("A6").End(xlUp)
While Worksheets("Calendar").Cells(rw, 1).Value = ActiveCell.Value

            If Worksheets("Calendar").Cells(rw, 4).Value = "Thyroid-Scan" Then
            

                   
Range("A" & Selection.Row & ":I" & Selection.Row).Copy LastRow.Offset(1, 0)
        
        End If
        
        rw = rw + 1
        
        Wend
 
  
rw = ActiveCell.Row
 

Set LastRow = Sheets("ThyroidTemplate").Range("A20").End(xlUp)
While Worksheets("Calendar").Cells(rw, 1).Value = ActiveCell.Value

            If Worksheets("Calendar").Cells(rw, 4).Value = "Thyroid - Treat" Then
            

                   
Range("A" & Selection.Row & ":I" & Selection.Row).Copy LastRow.Offset(1, 0)
        
        End If
        
        rw = rw + 1
        
        Wend
 
 Else
 
 MsgBox " Please Select The FIRST Thyroid Appointment For The Required Week."
 

End If


End Sub

Cheers Guys, looked everywhere on the forum for this

jjsteele

 

[Von Pookie]

What exactly is the code doing that it's not acting as expected?

I am curious as to why you have set the LastRow as an object, though. Also, why are you determining the last row by looking up from rows 6 and 20 instead of 65536 (the last possible row)?