Macro for moving selected item to new form

**Farrar** · 12-27-2012, 04:45 PM

I have a estimate form that i am trying to develop. In the past my form had a cover page with totals from detailed pages. The detailed pages had a list beside them of all the item that i might need in the estimate the detail page had pull down windows that i could scroll thruogh the list and pick the items that i needed. The list are getting very long and it is difficult to scroll through them and to add new items. I have attached my new estimate form that instead of having a list on the side i have added item list sheets, i need a add button with a macro that will send the item and the cost with mark up from the item sheet to the detail page for those items i.e. sewer items to sewer page, item to discription and cost with mark up to unit price. Estimate.xlsx

**daivd_vachon** · 12-27-2012, 05:17 PM

Hi Farrar,

I have taken a look at the file you provided, from what I gather you will now fill in several different forms, (Sewer, Water, etc) Then have the totals appear on the Cover page. I don't really see how you can avoid having drop down lists on the individual sheets. You would need to use a vlookup or index / match type formula to return the unit price for the "Description", if the user incorrectly names or spells the item in the Description column it would cause errors.

I may not have properly understood, the other way I understood it was that you would like to be able to double click any item in the list and have it appear in the appropriate list of costs?

If neither of these are correct please provide further explanation.

**Farrar** · 12-28-2012, 08:54 AM

New Estimate.xlsx Old estimate.xlsDaivd,

I am currently using the vlookup feauture, the look up list sits on the detail page off to the side. I find it difficult to scroll through the items and add items (which i do alot). What i would like to do is put all my items on a different sheet with a add button that i could scroll down and select multiple items and send the item into the discription colum and the cost with mark up into the unit price colum. i have attached an example off my old system and what i have made of the new system

**daivd_vachon** · 12-28-2012, 11:08 AM

I think the best solution would be to have items appear on the description column once they are double clicked from your list. Then once they appear in the description column use vlookup to get the price from the other sheet. The user then enters the quantity and the price would calculate. Would this work? It wouldn't be too complicated, I have made similar sheet, the only issue would be I have never done it over multiple sheets as in this case.

**Farrar** · 12-28-2012, 11:32 AM

Daivd,

That sounds very simalar to what i all ready have, except when i use my vlookup it adds the item to the discription and puts the price in the unit price all at one time. i am trying to get out of scrolling through all the items. i was reading another thread where a person had an inventory list she could hit an add button on that list and it would send that item to her sales quote. i would like to have something like that but i do not know how to use the macro's

**daivd_vachon** · 12-28-2012, 02:34 PM

Maybe I wasn't clear, you can create a macro that would allow you to double click on an item in another sheet and add it to your list, then once it is in the list your vlookup would work. I don't see how clicking and add button would be more efficient then just double clicking the actual item. Maybe I am missing something.

**Farrar** · 12-28-2012, 03:20 PM

daivd,

i think i understand know, i would double click on the item it would move to the details page that has the vlookup on it and at that point vlook up would fill in the unit price. that sounds like it would work. is there a way to make the vlookup list into a infinite list another problem i have is every time i need to add a item i have to redifine the area of the list.

**daivd_vachon** · 12-28-2012, 04:29 PM

I won't have time to write the macro today, I can likely try on Monday if no one else does by then, as for vlookup, I prefer Index / Match as it is easier to work with / around. Use the excel match function to return the row, then use index to return the value corresponding to the row and column.

**Farrar** · 12-28-2012, 05:31 PM

Daivd,

Thank you for your help

**xladept** · 12-28-2012, 07:16 PM

Hi Farrar,

This works but after selecting any sheet, you need to select a cell to see the button or for the button to disappear. It works one selection from the items sheet at a time:

EstimaterX.xlsm

And - you might want to change the sheet formula to:

=IF(C12*E12*1=0,"",C12*E12)

its a trick that I learned on this forum!

**Farrar** · 12-29-2012, 04:10 PM

xladept,

That works pretty good, i would like to select multiple items then send them to the detail page but what you have is is OK thank you very much

**xladept** · 12-29-2012, 04:40 PM

You're welcome - getting multiples would require some tricky coding, since I didn't attempt it, I have no suggestions.

I attempted it - this will work for a contiguous or non-contiguous -Multiple or Single

Sub Farrar(): Dim I As Range, ws As Worksheet, N As String, Description As String, Price As Single
N = ActiveSheet.Name: N = Left(N, Len(N) - 6): Set ws = Worksheets(N)
Dim r As Long, irow As Long: r = 11: Do: r = r + 1: Loop Until ws.Cells(r, 2) = ""
For Each I In Selection
If I.row > irow Then
Description = Cells(I.row, 2): Price = Cells(I.row, 3)
ws.Cells(r, 2) = Description: ws.Cells(r, 5) = Price: r = r + 1: irow = I.row: End If
GetNext: Next I: End Sub

It occurred to me that you might want to clear the sheets:

Sub ClearSheets(): Dim ws As Worksheet
For Each ws In Worksheets
If InStr(1, ws.Name, "Items") Then GoTo GetNext
ws.Range("B12:B43").ClearContents
ws.Range("E12:E43").ClearContents
GetNext: Next: End Sub

**Farrar** · 01-03-2013, 09:27 AM

xladept,

You will have to excuse me i do not know much about macro's, it took me a week to learn how to make a vlookup pull down window. I have tried to copy your formulas into my macro and i cannot figure out how to make it work.

**xladept** · 01-03-2013, 02:54 PM

Hi Farrar,

Sorry that I didn't provide instructions - I forgot that I had posted the whole book - you would copy the code and replace the code in module 1 with it - but here's the Workbook ready to go:EstimaterX.xlsm

**Farrar** · 01-03-2013, 05:43 PM

xladept,

That works very well, Thank you

**xladept** · 01-03-2013, 06:43 PM

You're welcome!

Macro for moving selected item to new form

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Re: Macro for moving selected item to new form

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions