Insert a message with paste

[jomili]

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.

Please Login or Register  to view this content.

[davesexcel]

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

[jomili]

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

[jomili]

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

[davesexcel]

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.

[jomili]

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.

[jomili]

This whole thing really boils down to one simple question, to which I still don't have an answer:

How do I insert a text phrase such as "Here's my text" into a cell via macro?