Forum Discussion

Mavnav's avatar
Mavnav
Copper Contributor
Jun 05, 2019

Price list - Order form-Summary page

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

excel
Formulas and Functions

6 Replies

    • Mavnav's avatar
      Mavnav
      Copper Contributor
      Jun 06, 2019

      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

      • Berndvbatanker's avatar
        Berndvbatanker
        Iron Contributor
        Jun 06, 2019

        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

        http://www.vba-tanker.com

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jun 05, 2019
    Please clarify your requirement by SPECIFYING your inputs and desired outputs.
    • Mavnav's avatar
      Mavnav
      Copper Contributor
      Jun 05, 2019

      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. 

       

Resources