Procedure for "Pasting to the next empty cell"

Posted by Larry H on November 21, 2000 8:12 AM

I have 2 XL worksheets that I use for Equipment Quotations. The "Catalog" sheet consists of descriptions of various equipment components and pricing information. These descriptions fill several rows and columns for each item

To create a quote, I copy all of the cells for the selected item, select the 2nd XL worksheet named "Newquote", and paste the copied cells to the new quote. If the quotation includes multiple items (it usually does) I then go back to my "Catalog", scroll down thru the various items until I find the next item to include in the new quote. I then repeat the process until I have copied and pasted all of the components that are to be quoted. This process takes me 10 to 15 minutes per quote. Everyone else requires twice the time and they frequently screw it up. While the process seems simple to me (I created it) it's too complex for some of my associates to comprehend.

My desire is to create an automatic version of the cut and paste process. By creating a cut and paste macro for each item, my associates could simply scroll through a list of item descriptions and click on the "Select" button that I can assign to each macro.

My problem is this...I can't find a way to automatically paste the selected item to the new quote. I need to paste each selection to the next unused cell in column A following the previously pasted item.

Posted by Ben O. on November 21, 2000 9:23 AM

Here's what you can use to activate your Newquote worksboook and select the next empty row:

Windows("Newquote").Activate
Columns("A:A").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

It first selects the entire A column, and then searches for the next empty cell. Then you could paste your selection with:

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Finally, you can go back to your Catalog with something like:

Windows("Catalog").Activate

Creating copy/paste macros for each item in your catalog sounds tedious though. You might want to consider a different method. Why not create a column on your catalog worksheet that has a check box linked to every cell. The user checks the items to be included in the new quote. When he's ready, he can run a macro that turns on the autofilter so only the checked items are displayed, then copies and pastes them all to a new or existing Newquote workbook.

If you'd like some help with that, let me know. I have a macro that does almost the same thing.

-Ben

Posted by Larry H on November 22, 2000 1:57 PM

Ben O,
Thanks for the response! I can't make it work. The macro bugs out when it gets to:

Selection.PasteSpecial.............

The Run Time Error dialog box says, "The information cannot be pasted because the copy area and the paste area are not the same size and shape. It suggests clicking on a single cell and then pasting.

When I check the "New Quote" worksheet, all of column A is selected...The routine doesn't select the next blank cell in column A.

I've attempted multiple fixes to no avail.

Your check box method is interesting...WAY over my head...but interesting.

Best regards

Posted by Ben O. on November 22, 2000 2:55 PM

hmmm...I should have known that that would be a problem. Try putting this line in before the Selection.Pastespecial line:

ActiveCell.Select

That will change the selection from the entire column to just the active cell.

I think that macro probably finds the next blank cell correctly, but when it finds it it still has the entire column selected. Anyway, ActiveCell.Select should fix that.

Good luck,

-Ben

Posted by Larry H on November 30, 2000 10:43 AM

Ben O,
Thanks for the help...that worked! By the way, you're correct that my process is tedious. However, I have found the code works great in another application that I've been working on. Now, I'll take your advice on the "Check Box" Links and autofilters. Hope I can figure that one out....(don't bet on it.)
Best regards