Copy Cells filled in from an excel spreadsheet form into a table

  • I have this form I send out and have personnel fill out and send back to me. Is it possible in VBA to copy all cells that are filled in and have them populate a table in a spreadsheet. I get 10 to 15 forms a day. Coping and pasting is a pain.
    Is this possible in excel?

    Thanks for looking at this.

    Attached a copy of form.

    VendorRepresentative
    Reason for ReturnModel #
    Installation DateSerial Number
    Mfg Date CodeCustomer Information
    P/O order/Invoice
    RGA (return good authorizationSerial Number Tag
    Vendor Supplied TagNone
    On Site Inspection
    Return To Vendor
    Return To Representative
    Field Scrap
    Fax Vendor Supplied FormHandling Charges May Apply - See Contact
    Shipping Charges May Apply - See Contact



    Excel tables to the web >> Excel Jeanie HTML 4

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    It would make it simpler if you replaced the multiple choice 'check boxes' with Dropdowns, probably using Data Validation - all assuming only 1 choice would be made from the list.


    One of the very few times I'd suggest you merge cells - both the Ship To and Special Instructions boxes.


    Once those are done, yes, it would be relatively easy...

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    cytop[INDENT] "It would make it simpler if you replaced the multiple choice 'check boxes' with Dropdowns, probably using Data Validation" - I enclosed my working form instead of a blank form
    Where I have a drop from Data Validation where the select a vendor and everything from the matrix fills in using an index formula
    "Once those are done, yes, it would be relatively easy..." I am still scratching my head and looking through out the site for a solution. Maybe something like this, with multiply ranges in a loop?

    Code
    Sub SummarizeSheets() 
        Dim ws As Worksheet 
        Application.ScreenUpdating = False 
        For Each ws In Sheets 
            ws.Range("19:19", "24:24").Copy 'I could select all the ranges
            Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues) 
        Next ws 
    End Sub

    to copy in another sheet in table form. I would need to modify this some how. I got this from

    HTML
    http://www.ozgrid.com/forum/showthread.php?t=178970

    I did away with the check boxes with Required to populate into him if that is what is on the matrix. What do you thing?? Thanks again for looking at it.[/INDENT]

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    Hi Smd747.
    To help you with your learning curve, I suggest you record a macro while you copy paste all fields from one form to the database and run it on subsequent forms. Use Relative References and select the first cell on the first empty row in the database before you start recording. In the form the active cell should be "A1". Before running the macro on new forms, you ensure that:
    - the name of the new forms is the same as the initial form and the name of the database is kept the same
    - both the form and the database have the relevant sheets activated
    - active cell in form is "A1" and active cell in database is the first cell in the first empty row in the database


    The result will be one of the worst VBA codes you will ever use. But it will work, slowly but surely. Having in mind the number of fields and forms/ day, I do not think speed is an issue for you. After you are sick with your new flickering, slow macro start upgrading it:
    - replace Select/ Copy with Range(in DB).Value=Range(in Form).Value
    - read this (http://www.cpearson.com/excel/ArraysAndRanges.aspx) and change your code accordingly
    - insert a code section in the macro that lets you choose the form file and the db file, using Application.GetOpenFilename
    - try upgrading to processing of multiple forms, using Dir
    - and so on ..


    Let me know if you have specific questions on any of the steps above.

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    DanXSS


    Thanks for the direction. I will read up and experiment and attempt to use the array solution and let you know how it works out. Interesting approach. Thanks for taking the time to replay. SMD747

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    An alternative to copying and pasting from fixed ranges...


    On a blank worksheet, add a series of cross references, something like:


    [table="width: 200"]

    [tr]


    [td]

    D1

    [/td]


    [td]

    B

    [/td]


    [/tr]


    [tr]


    [td]

    C3

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    C4

    [/td]


    [td]

    D

    [/td]


    [/tr]


    [tr]


    [td]

    I3

    [/td]


    [td]

    H

    [/td]


    [/tr]


    [tr]


    [td]

    I4

    [/td]


    [td]

    I

    [/td]


    [/tr]


    [/table]


    The first column represents the cells on the 'form', the second the column each field appears in on the 'database' sheet.


    To import the data you just need to get the last used row + 1 on the database sheet then loop down the Cross references reading from the cell stored in the first column of the cross references and and placing in the appropriate column/row of the database.


    The advantage is the structure changes to the form/database can be accommodated without having to change the code.

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    How would the loop work? would it be multiply loops for each field name. Loop D1, Loop C3? instead of copy it would send value to table? Can you show me an example? Thanks

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    Something like this...


    For simplicity, I used 3 sheets in the same workbook. You need to add code to identify and open the source workbook then set a reference to the worksheet in that workbook that contains the form data.


    I've added another sheet called Crossreference which contains a cross reference of a sample of the fields on the form and their corresponding column on the database sheet.


    Most of the code is concerned with setting up and getting ready to copy the data to the database, the actual copying is done by the simple 3 line loop near the end.


    Obviously this is a sample - it would need more work, error handling and probably some sort of data validation if used...

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    Cytop This is a great solution, It is what I was looking for. Your example is perfect with the notes explaining the steps. I see how the loops picks up the data from the Cross-reference sheet and the puts them in the table. Brilliant. I am amazed and would not have known where to start with out your example. Thank you for the direction and solution and education in VBA Thanks again

  • Re: Copy Cells filled in from an excel spreadsheet form into a table


    I would also like to thank DanXSS for his help explaining and Invaluable help in decoding and explaining another solution(http://www.cpearson.com/excel/ArraysAndRanges.aspx). For taking the time to help and direct me to a solution. His post was also very educational and helpful with learning VBA and working toward a solution. Thank you DanXSS. Here was his post.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!