Home

Price list - Order form-Summary page

%3CLINGO-SUB%20id%3D%22lingo-sub-671636%22%20slang%3D%22en-US%22%3EPrice%20list%20-%20Order%20form-Summary%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-671636%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20build%20an%20Ordering%20tool.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20my%20product%20families%20(5)%20on%20individual%20worksheets.%26nbsp%3B%3C%2FP%3E%3CP%3EThese%20are%20set%20ups%20to%20allow%20customers%20to%20enter%20a%20quantity.%26nbsp%3B%20That%20creates%20a%20discounted%20price%20and%20an%20extended%20price%20for%20all%20of%20the%20items%20in%20that%20family.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20copy%20out%20any%20lines%20that%20have%20a%20quantity%20entered%20into%20a%20summary%2Forder%20form.%3C%2FP%3E%3CP%3ESo%20this%20is%20like%20Filtering%20for%20a%20qty%20larger%20than%20zero%20and%20copying%20only%20those%20lines%20to%20a%20summary%20page.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20selectif%20kind%20of%20statement%3F%3C%2FP%3E%3CP%3EIs%20there%20another%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciate%20your%20help!%3C%2FP%3E%3CP%3Emav%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-671636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-671864%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20list%20-%20Order%20form-Summary%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-671864%22%20slang%3D%22en-US%22%3EPlease%20clarify%20your%20requirement%20by%20SPECIFYING%20your%20inputs%20and%20desired%20outputs.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-672361%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20list%20-%20Order%20form-Summary%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672361%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355281%22%20target%3D%22_blank%22%3E%40Mavnav%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20a%20little%20demo%20file%20would%20help%20us.%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673107%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20list%20-%20Order%20form-Summary%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673107%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Einput%20worksheets%20are%20product%20numbers%2C%20descriptions%2C%20and%20prices%20with%20a%20qty%20column.%20%26nbsp%3BWhen%20quantities%20are%20added%20it%20calculates%20an%20extended%20price.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BThere%20may%20be%20as%20many%20as%20500%20individual%20products%20in%20a%20worksheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20are%20several%20of%20the%20product%20work%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20write%20a%20summary%20page%20that%20looks%20at%20all%20of%20the%20input%20worksheets%20and%20selects%20any%20lines%20that%20have%20quantities.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20ave%20a%20work%20around%20version%20by%20having%20the%20summary%20contain%20all%20the%20lines%20in%20all%20of%20the%20sheets%20and%20then%20filtering%20out%20zero%20value%20lines.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20be%20easy%20in%20Access%20where%20we%20could%20write%20a%20query%20to%20pull%20only%20non%20zero%20lines.%20But%20I%20am%20trying%20to%20provide%20a%20simple%20reliable%20tool%20for%20customers.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-674584%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20list%20-%20Order%20form-Summary%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674584%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help.%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20miniature%20example.%26nbsp%3B%20There%20are%20two%20Worksheets%20that%20hold%20product%20info%20and%20a%20column%20to%20allow%20a%20number%20to%20be%20entered%20(to%20indicate%20an%20desired%20order%20quantity.)%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20relates%20to%20pulling%20all%20of%20those%20non-zero%20lines%20into%20a%20clear%20concise%20order%20form.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20may%20be%20a%20case%20where%20I%20am%20asking%20excel%20to%20do%20things%20that%20a%20data%20base%20could%20do%20better%20but%20I%20am%20looking%20for%20an%20easy%20tool%20to%20deploy%20with%20customers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20Thank%20you%20for%20taking%20a%20look.%3C%2FP%3E%3CP%3Emav%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-674895%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20list%20-%20Order%20form-Summary%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355281%22%20target%3D%22_blank%22%3E%40Mavnav%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Mav%2C%3C%2FP%3E%3CP%3Ei%20make%20a%20macro%20for%20you%2C%20see%20the%20attachment%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20GetData()%3CBR%20%2F%3EDim%20wksTab%20As%20Worksheet%3CBR%20%2F%3EDim%20wksTabTarget%20As%20Worksheet%3CBR%20%2F%3EDim%20lngz%20As%20Long%3CBR%20%2F%3EDim%20rngFind%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3Elngz%20%3D%207%3CBR%20%2F%3ESet%20wksTabTarget%20%3D%20Worksheets(%22Order%20summary%20Sheet%22)%3CBR%20%2F%3EwksTabTarget.Range(%22A7%3AC%22%20%26amp%3B%20wksTabTarget.Rows.Count).Clear%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20wksTab%20In%20ThisWorkbook.Worksheets%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rngFind%20%3D%20wksTab.Range(%22D%3AD%22).Find(what%3A%3D%22SubTotal%22%2C%20lookat%3A%3DxlPart)%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20rngFind%20Is%20Nothing%20Then%3CBR%20%2F%3EIf%20rngFind.Offset(0%2C%201).Value%20%26lt%3B%26gt%3B%200%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EwksTabTarget.Range(%22A%22%20%26amp%3B%20lngz).Value%20%3D%20%22SubTotal%22%3CBR%20%2F%3EwksTabTarget.Range(%22B%22%20%26amp%3B%20lngz).Value%20%3D%20wksTab.Name%3CBR%20%2F%3EwksTabTarget.Range(%22C%22%20%26amp%3B%20lngz).Value%20%3D%20rngFind.Offset(0%2C%201).Value%3CBR%20%2F%3Elngz%20%3D%20lngz%20%2B%201%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20wksTab%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%20from%20germany%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%20-%20a%20database%20full%20of%20usefull%20macros%20-%20more%20info%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677535%22%20slang%3D%22en-US%22%3ERe%3A%20Price%20list%20-%20Order%20form-Summary%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677535%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You.%26nbsp%3B%20I%20appreciate%20the%20help!%3C%2FP%3E%3CP%3Emav%3C%2FP%3E%3C%2FLINGO-BODY%3E
Mavnav
Occasional Contributor

I am trying to build an Ordering tool. 

I have my product families (5) on individual worksheets. 

These are set ups to allow customers to enter a quantity.  That creates a discounted price and an extended price for all of the items in that family. 

I would like to copy out any lines that have a quantity entered into a summary/order form.

So this is like Filtering for a qty larger than zero and copying only those lines to a summary page.

 

Is there a selectif kind of statement?

Is there another way to do this?

 

I appreciate your help!

mav

6 Replies
Please clarify your requirement by SPECIFYING your inputs and desired outputs.

@Mavnav 

Hi, a little demo file would help us.

Regards

Bernd

@Twifoo 

input worksheets are product numbers, descriptions, and prices with a qty column.  When quantities are added it calculates an extended price. 

 There may be as many as 500 individual products in a worksheet. 

 

There are are several of the product work sheets. 

 

I want to write a summary page that looks at all of the input worksheets and selects any lines that have quantities. 

 

I have ave a work around version by having the summary contain all the lines in all of the sheets and then filtering out zero value lines. 

 

This would be easy in Access where we could write a query to pull only non zero lines. But I am trying to provide a simple reliable tool for customers. 

 

Thank you for your help. 

 

@Berndvbatanker 

 

Thank you for your help.

I have attached a miniature example.  There are two Worksheets that hold product info and a column to allow a number to be entered (to indicate an desired order quantity.) 

My question relates to pulling all of those non-zero lines into a clear concise order form. 

 

This may be a case where I am asking excel to do things that a data base could do better but I am looking for an easy tool to deploy with customers.

 

Again Thank you for taking a look.

mav

@Mavnav 

Hi Mav,

i make a macro for you, see the attachment too.

 

Sub GetData()
Dim wksTab As Worksheet
Dim wksTabTarget As Worksheet
Dim lngz As Long
Dim rngFind As Range

lngz = 7
Set wksTabTarget = Worksheets("Order summary Sheet")
wksTabTarget.Range("A7:C" & wksTabTarget.Rows.Count).Clear

For Each wksTab In ThisWorkbook.Worksheets

Set rngFind = wksTab.Range("D:D").Find(what:="SubTotal", lookat:=xlPart)

If Not rngFind Is Nothing Then
If rngFind.Offset(0, 1).Value <> 0 Then

wksTabTarget.Range("A" & lngz).Value = "SubTotal"
wksTabTarget.Range("B" & lngz).Value = wksTab.Name
wksTabTarget.Range("C" & lngz).Value = rngFind.Offset(0, 1).Value
lngz = lngz + 1

End If

End If

Next wksTab

End Sub

 

Best regards from germany

Bernd

www.vba-tanker.com - a database full of usefull macros - more info

@Berndvbatanker 

 

Thank You.  I appreciate the help!

mav

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies