Write Data in Sheet2 when user enters qty in Sheet1 1

[Dryheat]

I checked the FAQ's and could not think of a way to run a search for this topic. So if it's been covered aleady then please forgive me.

4 columns in Sheet1 contain a list of items.
Col A is for QTY
Col B is for Item
Col C is for Description
Col D is for Pric

I would like to write code that would take the data from all four columns and write that data to Sheet2.
Ok, here's the catch (actually two catches)
1. I only want to write data from rows that contain a qty in Col A
2. There are several other sections of 4 column lists which I need to handle as well.

If not for catch number 2 above I could do this very simply with an MS Query. But with 4-5 different sections for storing item information the MS Query approach becomes extremely cumbersome.
Thanks for any ideas you come with

Dry

 

[SkipVought]

Hi,

"1. I only want to write data from rows that contain a qty in Col A"

Might there also be values in coluums B-D if there is no value in column A?

"2. There are several other sections of 4 column lists which I need to handle as well."

How are these "other sections" defined?

Have you tried macro recording a Copy 'n' Paste to one of these sections?

What code do you have?

Please answer ALL questions clearly, concisely and completely.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

I have no code. Not even started. I have experience in coding MS Access but no Excel so I am not familiar with nagvagting cells to search for data or writing data to specific cells.

1. "Might there also be values in coluums B-D if there is no value in column A?"
Yes but if there is no Qty entered I need to skip those rows. I only want to copy the rows where the user has entered a qty.

2. "How are these "other sections" defined?"

 

[Dryheat]

Sorry, hit the submit button to soon. I was trying to upload a sample xls to answer number 2 and pressed the wrong button. I do not have access to upload the file from work so I will have to do it from home.

The best answer I can give without showing you a file is there are merged cells all over this sheet to present a clean looking document to the customer. So all the items are not listed in a straight column from top to bottom. There is a main item selection area and then several other sections created for accesories and supplies. the Qty and Item numbers do not always appear in the same order from left to right.

Dry

 

[SkipVought]

So what event kicks off this process?

Really need to know more about the targets for this data. AND merged cells make the task a bit more complex.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

Event would be a control button, lets lable it 'Submit'.
I really just need a staring point. Let say I have a function that runs when the submit button is pressed. The fuction needs to read all the cells defined as storing qty information and only if it finds a value in the qty cell it should then copy the related cells in that row and paste the data into next available row in the other sheet.

This is supposed to allow a customer to choose the items he wants to order and then generate a list of items in another sheet based on those selections.

Dry

 

[SkipVought]

You describe what could be a fairly simplesolution.

Suppose that ALL the data were on ONE sheet in ONE table.

The QTY column is the only column that the user can enter data into.

The AutoFilter is on.

When the user is done entering quantities, they hit the SUBMIT button and the AutoFilter displays NON BLANK values in the QTY column.

I suppose that each entered QTY, results in an extension of a PRICE to give a TOTAL.

The CLEAR or NEW INVOICE button, ShowsAll data and clears the data in the QTY column.

Pretty simple!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

Not so simple. I wish it was. See catch number 2 from original post. To present a clear and easy to use document to the customer the data is grouped into multiple sections on the worksheet. Each section has it's own format and merged cells. If all the data could be arranged neatly into consistant columns from top the bottom the easiest solution is an MS Query. But this is not the case so VB code is required. Can someome give me some basic exapmles of how to copy values from one cell and paste into anohter cell? Then I need to know to reference a cell in an if statment to test if there is a value. Once I have the basics down I can work out the rest. I just don't have any experience coding Excel.

Dry

 

[SkipVought]

You are not providing very much help to work on, Dry!

dim lFirstRow as long, lLastRow as long, lRow as long, iCol as integer, lRowOut as long, iColOut as integer
with Sheet1
  lFirstRow = .usedrange.row
  lLastRow = .usedrange.rows.count + lFirstRow - 1
  for lRow = lFirstRow to lLastRow
     with .cells(lRow, 1)
        if trim(.value) <> "" then
'I have not idea where your sections are so lRowOut & iColOut are UNKNOWN to me...
           For iCol = 0 to 3
              Sheet2.Cells(lRowOut, iColOut+iCol).value = .Offset(0, iCol).value
           next
        end if
     end with
  next
end with

gets you started, perhaps.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

Skip, that is exactly what I was looking for. I can get started with this. Now that I am at home I can upload that sample file so you can see what it looks like. There are many more sections in the live version but for now I have only included two.
Check the attached file.

Dry

 

[SkipVought]

This is a nightmare!

Your sections are not even related with respect to column data!

Description <=> Description!!!!????

Question: You are supposedly taking 4 columns of data from one sheet (unshown in your example workbook) and copying to, what: the end of each section on the other sheet? It appears that that might only work in the first section, which also has 4 columns, but not section 2.

Do rows get inserted between sections when there's not enough room?

Your example does little to clarify, but raises a hord or questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

I'll try to explain. First this example from a co-worker who is trying to create a tool for his customer's to use for calculating their cost to install an air conditioner. The format on 'Cust Catalog' sheet is formatted to make it easy for the customer to use, instead of being easy to program. I'm not going to try to change this.
The request is the customer should be able to enter quantities in the 'Cust Catalog' sheet and only those items selected will appear on the 'Material List' sheet. Once complete the Material list can be printed and faxed to the vendor as a Purchase Order. There will be some other calculations performed on another sheet using the material list to add in the customer's cost of labor and material to finally create a sell price to the end user.
Something like this could easily be accomplished using MS Access but this co-worker has little or no experience in MS Access so I'm not going to try to change that either.
There has to be some sort of method in Excel VBA that allows you to append data to the end of the table object???
And it should be pretty simple to write a separate sub routine for each section on the cust catalog sheet that would append those items to the bottom of the table.
The logic is pretty easy for me to work out it's just my unfamiliarity with Excel VBA prevents me from deriving a quick solution.
Thanks for your time.

Dry

 

[SkipVought]

What we have here is a failure to communicate.

FINALLY we know that Sheet1 is the Cust Catalog and the other sheet is the Material List.

You ALSO said, "4 columns in Sheet1 contain a list of items."

However, your example workook has one section that has FIVE columns (one merged)

So on the Cust Catalog sheet, there are a number of sections, where ...

1. the quantity column is different and
2. the quantity column HEADING is different.

We have to know what we are looking for, Dry.

Please explain ALL the nuances that logic might be different in processing the Cust Catalog sheet.

It is becoming clearer now.

How many sections?

What are the Section Headings? is the

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

I just need to know how to do the sections on the sample. The live document has several sections and some more disimiler than the two I gave. It's irrelavant to show the entire document and I don't need someone to right the entire code for me. Just show how I might manage to deal with the two sections in the sample and I can finish it from there. Once I see how to do this for the sample given it will be easy for me to modify the code to do the rest.
Thanks.

Dry

 

[SkipVought]

1, Set up a SectionName table like ...
[tt]
SectionName
Section 1
Section 2
[/tt]
2. Set up a mapping Table to describe each section like...
[tt]
SectName Heading From To Cost
Section 1 QUANTITY 1 1
Section 1 Description 2 2
Section 1 Item 3 3
Section 1 Price 4 4 1
Section 2 DESCRIPTION 1 2
Section 2 PART NUMBER 3 ?
Section 2 QTY. 4 1
Section 2 COST 5 4 1
...
[/tt]
Name the ranges in the Section & Mapping Table heading name Insert > Name > Create - Create names in TOP ROW

The process will use the FIND method, using the Mapping Table as the source.

Sub Main()
    Dim rFound As Range, sPrev As String, r As Range, rCat As Range, rMap As Range
    Dim lRow As Long, vCust
      
    lRow = 2
    For Each r In [SectionName]
        If sPrev = "" Then  'first section
            Set rFound = Sheets("Cust Catalog").[A1]
        Else
            Set rFound = Sheets("Cust Catalog").Cells.Find(r.Value)
        End If
        With rFound.CurrentRegion
            Set rFound = Range(rFound.Offset(2), .Cells(.Rows.Count, 1))
        End With
        
        For Each rCat In rFound
            For Each rMap In [SectName]
                If rMap.Value = r.Value Then
                    vCust = Sheets("Cust Catalog").Cells(rCat.Row, Sheets("Mapping").Cells(rMap.Row, [From].Column)).Value
                    If vCust > 0 Then
                        Sheets("Material List").Cells(lRow, Sheets("Mapping").Cells(rMap.Row, [To].Column)).Value = vCust
                    Else
                        lRow = lRow - 1
                        Exit For
                    End If
                End If
            Next
            lRow = lRow + 1
        Next
        
        sPrev = r.Value
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

Awesome. Thanks Skip. I'll chew on this for a while and while and tailor it to my co-worker's workbook.
Very grateful for you patience and knowledge.
Thanks!

Dry

 

[SkipVought]

BTW, The Mapping table needs to be changed from
[tt]
SectName Heading From To Cost
[/tt]
to
[tt]
SectName Heading From To QTY
[/tt]
and the sorted by SectName ASC, & QTY DESC. This is to be able to check the QTY first off and process the row or not.
[tt]
SectName Heading From To QTY
Section 1 QUANTITY 1 1 1
Section 1 Description 2 2
Section 1 Item 3 3
Section 1 Price 4 4
Section 2 QTY. 4 1 1
Section 2 DESCRIPTION 1 2
Section 2 PART NUMBER 3 3
Section 2 COST 5 4
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dryheat]

Thanks again Skip. I just had a few minutes to copy this into my sample spread sheet and it works perfectly.
You are awsome!

Dry

 

[SkipVought]

Glad I could help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}