Results 1 to 7 of 7

Insert a message with paste

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,065

    Insert a message with paste

    The routine below sorts a spreadsheet by a certain criteria, then copies the visible cells and pastes into another spreadsheet. I've added (don't know how well) a section that will pop up a message if no valid criteria exists to sort. The copied cells will be incorporated into a larger report, so the example code below is for only the first step of 9 different steps, each resulting in a copy/paste.

    I'd like to be able to do two additional functions:

    1) For each selection I copy/paste, add Text either before or after saying something like "Step 1 Completed: Results are Below" (or "Above")

    2) For a selection resulting in invalid criteria, don't copy anything, instead paste something like "Step 1 Completed: No results were found for this selection"

    Here's the code; I've tried to note what each section does, and would appreciate any help cleaning it up and augmenting the functionality.

    Thanks.

    Sub Step_1()
    
      Dim DstWkb As Workbook
      Dim Rng As Range
      Dim RngEnd As Range
      Dim cell As Range
      Set Rng = Selection
      
      Set DstWkb = Workbooks("APR Checklist_working.xls")
    'Restrain the filter to cells from A1 to the last entry in column W
              With DstWkb.Worksheets("Data")
                Set Rng = .Range("A1:W1")
                Set RngEnd = .Cells(Rows.Count, Rng.Column).End(xlUp)
                Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, .Range(Rng, RngEnd))
              End With
             'Filter the data using column M
              Rng.EntireRow.Autofilter Field:=13, Criteria1:=("0")
    
              'Display a message if no valid criteria exists
              If Rng.Columns(1).SpecialCells(xlVisible).Count - 1 = 0 Then
              MsgBox "Good Work!  You have no Temporary FTEs!"
                     
              End If
              'Trap the error if there were no matches
              On Error Resume Next
    
               'Copy and paste only the filtered data
               Range("J1").Activate
            Rng.SpecialCells(xlCellTypeVisible).Copy _
                    Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
                
                'Non-working attempt to paste a message if no valid criteria exists
            If Rng.Columns(1).SpecialCells(xlVisible).Count - 1 = 0 Then
                PasteSpecial ("Step 1 Completed:  No Temporary FTEs found")
            End If
            
             'Clear the error if there was one
              Err.Clear
             'Return error control back to the system
              On Error GoTo 0
    End Sub
    Last edited by Mordred; 08-30-2011 at 12:56 PM. Reason: Clarification

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1