Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    New Lounger
    Join Date
    Mar 2013
    Location
    Michigan
    Posts
    14
    Thanks
    5
    Thanked 1 Time in 1 Post
    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA!

    I just ran testing for the demo in the morning - for some reason all the "moves" are working except for the DISQUALIFIED. It moves....but I have no idea where it goes but it isn't going to the DISQUALIFIED tab....yikes! record just disappears. I see no typo's or anything to explain this. Setup is the same as all the rest of the tabs - and they work. HELP

  2. #17
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Quote Originally Posted by ShannyR View Post
    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA!

    I just ran testing for the demo in the morning - for some reason all the "moves" are working except for the DISQUALIFIED. It moves....but I have no idea where it goes but it isn't going to the DISQUALIFIED tab....yikes! record just disappears. I see no typo's or anything to explain this. Setup is the same as all the rest of the tabs - and they work. HELP
    Works fine on my copy! Double check the spelling and insure there are no leading or trailing blanks on the sheet tab.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #18
    New Lounger
    Join Date
    Mar 2013
    Location
    Michigan
    Posts
    14
    Thanks
    5
    Thanked 1 Time in 1 Post
    No leading or trailing spaces. Record disappears from message board, but doesn't go to DISQUALIFIED. Doesn't appear to go anywhere - just deletes. What did I miss? I aattached test file
    Attached Files Attached Files

  4. #19
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Shanny,

    Ok to make the last request work you first have to UnFreeze Panes on the Message Board Sheet and delete rows 1:3. I don't know why they were there but the foul up the logic when moving items back to that page! When attempting this type of work it is best if all the sheets where you are moving records between are setup EXACTLY the same. Deleting these lines will cause an error in the macro code, just press the End button. You'll next have to use the Immediate Window in the VBA Editor and enter the command:
    Application.EnableEvents = True
    That command will reset Events to on since the macro errored out while they were off.

    Now you need to copy the code in the Message Board sheet to the other 4 sheets.

    Lastly replace the code in the Module1 with this code:
    Code:
    Option Explicit
    
    Sub MoveDeleteRecord(Target As Range)
    
       Dim oDestSheet   As Worksheet
       Dim oSourcesheet As Worksheet
       Dim lCurRow      As Long
    
          Application.ScreenUpdating = False
          Set oSourcesheet = ActiveSheet
          lCurRow = Target.Row
    
          Select Case UCase(Target.Value)
                Case "C"
                    Set oDestSheet = Sheets("Completed")
                Case "D"
                    Set oDestSheet = Sheets("Disqualified")
                Case "I"
                    Set oDestSheet = Sheets("Inquiry Only")
                Case "A"
                    Set oDestSheet = Sheets("Abandoned")
                Case ""
                    Set oDestSheet = Sheets("Message Board")
                Case Else:
                    MsgBox "Invalid code entered...please try again.", _
                           vbInformation + vbOKOnly, "Error: Invalid Code"
                    Exit Sub
          End Select
          
          If oDestSheet.Name = oSourcesheet.Name Then
            MsgBox "Record is already located on the " & oDestSheet.Name & _
                   " sheet!" & vbCrLf & vbCrLf & _
                   "No action taken!", vbCritical + vbOKOnly, _
                   "Error: Invalid ? code"
            Exit Sub
          End If
       
          With Cells(lCurRow, 13)
              .Value = Now()
              .NumberFormat = "mm/dd/yy hh:mm tt"
          End With
          
          With Cells(lCurRow, 14)
              .FormulaR1C1 = "=DateDif(RC2,RC13," & Chr(34) & "D" & Chr(34) & ")"
              .NumberFormat = "General"
          End With
          
          oDestSheet.Activate
          [A1].End(xlDown).Select
          
          If ActiveCell.Row = Rows.Count Then
            [a2].Select
          Else
           Selection.Offset(1, 0).Select
          End If
          
          '*** Move back to Message Board clear Cols M & N ***
          If oDestSheet.Name = "Message Board" Then
            With Selection
                .Offset(0, 13).ClearContents
                .Offset(0, 14).ClearContents
            End With
          End If
          
          
          oSourcesheet.Activate
          Rows(lCurRow).EntireRow.Copy
          oDestSheet.Activate
          ActiveSheet.Paste
          Application.CutCopyMode = False
          
          oSourcesheet.Activate
          Rows(lCurRow).EntireRow.Delete
    
    End Sub
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    ShannyR (2013-03-21)

  6. #20
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Shanny,

    I just couldn't get this little problem out of my head.

    Ok to make the last request work you first have to UnFreeze Panes on the Message Board Sheet and delete rows 1:3. I don't know why they were there but the foul up the logic when moving items back to that page! When attempting this type of work it is best if all the sheets where you are moving records between are setup EXACTLY the same.
    I was thinking about it when I was pedaling the stationary bike in PT today and came up with a fix.
    If you replace this code in the MoveDeleteRecord routine in Module1:
    Code:
          [A1].End(xlDown).Select
          
          If ActiveCell.Row = Rows.Count Then
            [a2].Select
          Else
           Selection.Offset(1, 0).Select
          End If
    With this one line:
    Code:
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    You can keep those 3 blank rows you had at the beginning of Message Board.

    NOTE: This assumes there is nothing on the sheet except the table/list, which was not the case with your test data but I assumed that was from previous attempts and not actual data.

    This is the best solution that I have ever come up with for the finding the bottom of a list as it will work on any version of Excel thanks to the Rows.Count, will work even if the entire sheet is blank and/or there are blank lines above the list as in your case and it's all in one line of code. The only caveat is that if column 1 is not the longest filled in column in the table you would need to change it to the appropriate number for the appropriate column. However, in most cases the 1st column is the one that is always filled in of course you could write it as a callable subroutine and pass it the proper column in case it is different on different sheets and you are using common code as:
    Code:
    Sub FindLastEntryRow(lKeyCol as long)
    
       Cells(Rows.Count, lKeyCol).End(xlUp).Offset(1, 0).Select
    
    End Sub 'FindLastEntryRow
    HTH
    Last edited by RetiredGeek; 2013-03-22 at 19:36.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •