90% there with code, small problem!!

jjsteele

New Member
Joined
Oct 5, 2004
Messages
35
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.

Code:
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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)?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,858
Members
414,408
Latest member
macroSmith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top