Jun 05 2019 09:42 AM
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
Jun 05 2019 11:12 AM
Jun 05 2019 01:27 PM
Jun 05 2019 09:08 PM
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.
Jun 06 2019 06:16 AM
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
Jun 06 2019 08:02 AM
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
Jun 07 2019 06:20 AM