Macro to move rows and organize

**kscott** · 03-04-2009, 05:37 PM

Hello all,

I have been trying to do this for 3 days using "Record Macro", but something always seems to be messed up. The main problem is that the worksheets are protected and the macro asks for the password.

Here is a calendar sheet that I've been working on. It contains the calendar on sheet 1(not important) and a client roster on sheet 2. The client roster gets filled quickly, and obviously gets unorganized. What I'd like to do is create a macro so that when a date is entered into column AA (when a client is discharged), that entire row is moved onto sheet 3 (Discharged). At the same time, I'd like all of the names in column A alphabetized.

I even tried to create an "Organize" button so that the users could just click it and alphabetize. The problem I ran into with that was that the users are using Office 2000, and the names get alphabetized but all of their information no longer matches up.

I apologize for the extended explanation.

**Leith Ross** · 03-04-2009, 09:51 PM

Hello kscott,

The following macro has been added to the attached workbook. This copy the discharge patient information and then sort it alphabetically when the discharge date is entered.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim DstRng As Range
  Dim LastCol As Long
  Dim LastRow As Long
  Dim NextRow As Long
  
    If Target = "" Or Target.Row = 1 Or Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> Columns("AA").Column Then Exit Sub
    
      Set DstRng = Worksheets("Discharged").UsedRange
      Application.EnableEvents = False
      
        With DstRng
          LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
          LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
          NextRow = IIf(LastRow < 2, 2, LastRow + 1)
        End With
       
        Target.EntireRow.Copy Destination:=DstRng.Cells(NextRow, "A")
        
        Set DstRng = DstRng.Resize(NextRow, LastCol)
        DstRng.Sort Key1:=DstRng.Cells(1, 1), Order1:=xlAscending, Header:=xlYes, _
                    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
          
     Application.EnableEvents = True
     
End Sub

**kscott** · 03-04-2009, 11:49 PM

Thank you for helping, and I hope this doesn't sound unappreciative.

I might be doing something wrong, but I'm willing to bet I didn't explain something well. Maybe this will sound better. The "Record Macro" feature I've been working on is assigned to a "button" on the top, and it alphabetizes the "Roster" tab. Ideally I would like that button to alphabetize, but first I would like every client (row) that has a date in the "Discharge Date" column to be moved onto the "Discharge" tab.

The organization of the workbook will be alphabetized, and all clients that have a date in the discharge column will be removed from the Roster tab and placed into the Discharge tab (hopefully also alphabetized, but that's just for convenience).

One more question-If the row is cut and paste into a different tab, the rows will eventually run out, right? Will adding new rows mess up the macro? Or is there a way to just move the data?

Thanks again, this will really save a ton of time for about 20 people!

**Leith Ross** · 03-05-2009, 12:29 AM

Hello kscott,

I wrote the macro based upon what you said in your previous post.

Here is a calendar sheet that I've been working on. It contains the calendar on sheet 1(not important) and a client roster on sheet 2. The client roster gets filled quickly, and obviously gets unorganized. What I'd like to do is create a macro so that when a date is entered into column AA (when a client is discharged), that entire row is moved onto sheet 3 (Discharged). At the same time, I'd like all of the names in column A alphabetized.

I am closing this thread as it "solved". If you want answer to your last post, you will need to start a new thread.