+ Reply to Thread
Results 1 to 7 of 7

Insert a message with paste

Hybrid View

jomili Insert a message with paste 12-14-2009, 11:56 AM
davesexcel Re: Insert a message with... 12-14-2009, 10:03 PM
jomili Re: Insert a message with... 12-15-2009, 10:22 AM
jomili Re: Insert a message with... 12-15-2009, 04:08 PM
davesexcel Re: Insert a message with... 12-15-2009, 09:32 PM
  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

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Insert a message with paste

    It would be best to supply a sample workbook with this. Nobody has the same workbook as you have.

  3. #3
    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

    Re: Insert a message with paste

    Dave,

    I'm not sure how posting the spreadsheets could help on this one, but I'm glad to do it; maybe you can help with some other issues, too, or point out a better way to do things.

    Procedure: first, open SourceData.xls. This is, as you can guess, the source data. Then open Final.xls.

    1) "Save" prompt: Here's the first problem; Final should display a message to save SourceData as APR. The problem is the prompt for this doesn't appear until you select the sheet. Is there a way to make it pop up right away?

    2) Select your region, hit the GO button. Series of macros run, copying, pasting, displaying messages. Here's the problem I came in with: I want to paste the results messages into the "Results" page along with the data. Ideally I'd want to do this:

    Copy BEFORE the data: Appropriate Text from column c of "Steps" worksheet in Final.xls
    Macro sorts data, copies and pastes selection
    AFTER the data: Paste message, either "No Results Found" or "X (qty) of Results Found" depending on the outcome of the test.
    AFTER the message: Paste appropriate selection from column D of "Steps" worksheet in Final.xls. (columns C and D line up with column B)

    Onthe "Results" worksheet I'd like it to look something like this:

    Step 1: Procedure
    Text from column C of "Steps"
    Step 1: Results (this is where the data would be pasted.
    Step 1: Qty of Results found (if 0, no data should be pasted other than this line)
    Step1: Solution
    Text from column D of "Steps"
    (Blank Line)
    Step 2: Procedure, etc.

    I appreciate any help you can provide.

    Thanks,
    John
    Attached Files Attached Files

  4. #4
    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

    Re: Insert a message with paste

    Two days, and no advice yet? I'm disappointed!

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Insert a message with paste

    I am disappointed in your samples, do you really expect somebody to waste a bunch of time trying to figure out what you are doing?

    Make your question simple and your samples simple.

  6. #6
    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

    Re: Insert a message with paste

    Dave,

    Sorry. I thought my samples were pretty straightforward, and my question a pretty simple one. I don't know that I can make it any more simple, but I'll try:

    I want to select and copy data from Worksheet A and from Worksheet C and paste it into worksheet D in a specific order, Skip a line, then select and copy different data from Worksheet A and C and paste it under the previous paste. I also would like to insert a message based on the selection (For instance, if my selection has 5 lines, paste the message ""5 Results Found")

    Please let me know how I can make this any simpler if it's still unclear.

+ Reply to Thread

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