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.

Excel tables to the web >> Excel Jeanie HTML 4

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?

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

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

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

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

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.

Quote from DanXSS

Some examples of how I use it. I am also new to VBA, started seriously last year, so there may be better solutions out there. However loading data from worksheet using array is the fastest, according not only to cpearson, but also to others that did thorough testing.

Myself I upgraded a 3 minutes simulation (which used read/ write worksheet loops) using array method and the final result was last than 1 second. So first step is to remove worksheet/ cell activation/ selection:
- instead of Range("A1").Copy and Range("A2").PasteSpecial xlPasteValues
- use Range("A2").Value = Range("A1").Value
Also:
- instead of Worksheets("abc").Activate and then use range as Range("A1").Value,
- use Worksheets("abc").Range("A1").Value or
- use named ranges (at workbook level): Range ("Name").Value

Even faster is to use intermediary arrays to load the worksheet data, do whatever transformation you need in VBA and then insert the results back in the worksheet.

Some golden rules on cpearson method, it was difficult for me to grasp:
1. You can insert in the worksheet almost any kind of array you want: single dimension or bi-dimensional, static or dynamic, etc, but you can only load to a dynamic, unallocated array of variants (Array() as Variant). Maybe it works with other type of arrays (Array() as Double), but I prefer Variant, since I do not always know in advance what I will end up loading, so better to use an intermediary variant first, test for data type and then loaded to your clean slate dedicated arrays( date, long, double, string etc)
2. Single arrays in VBA are "horrizontal", if you insert it in Excel they will be written in a single row, from left to right. If you need it vertical in the sheet, than use the Application.Transpose(Array()) function.
3. In Bi dimensional arrays, the first dimension is "vertical" (!!!, opposite to the single array design) and its indexes are the row relative numbers, while the second is "horizontal" and its indexes are the column relative numbers.

Some examples of how I use this:

First, loading:

Code

Dim InterArray() As Variant ' used to process ranges of worksheet data


     'Loading and testing Probabilities
     InterArray() = Range("_MProbabilities").Offset(1, 0).Resize(ProbMaxCount, ProbColumns)
     i = 1
     Do While InterArray(i, 1) <> ""
         i = i + 1
     Loop
     ProbCount = i - 1




     ReDim ProbName(1 To ProbCount)
     ReDim ProbDescr(1 To ProbCount)
     ReDim ProbFrom(1 To ProbCount)
     ReDim ProbTo(1 To ProbCount)




     For x = 1 To ProbCount
         ProbName(x) = InterArray(x, 1)
         ProbDescr(x) = InterArray(x, 2)
         ProbFrom(x) = InterArray(x, 3)
         ProbTo(x) = InterArray(x, 4)
     Next x
     Erase InterArray()

Display More

Second, writing back to worksheet:
1. horrizontally:

Code

Range("_MScenarioOccurence").Offset(0, 0).Resize(1, ScenarioCount).value = ScenarioName()

2. vertically:

Code

Range("_MOutputMultiples").Offset(1, 3).Resize(RunsCount, 1).value = Application.Transpose(HP1Multiples())

3. bi-dimensional:

Code

ReDim ValueImpactMatrix(1 To RunsCount * CompanyCount, 1 To ImpactMatrixColOffset + ScenarioCount)
     '......
     Range("_MOutputImpactV").Offset(1, 0).Resize(RunsCount * CompanyCount, ImpactMatrixColOffset + ScenarioCount).value = _
         ValueImpactMatrix()

So you need to size your target range accordingly, otherwise ... "f the array being passed to the worksheet is smaller than the Range to which it is written, the unused cells get a #N/A error. If the array being passed is larger than the range to which it is written, the array is truncated on the right or bottom to fit the range."
As you can see in bi-dimensional, I have sized the range as NumberOfRows = first dimension, NumberOfColumns = second dimension. You can loose Offset and Resize if you can pinpoint directly the range location and size.

Also, if you work with arrays with lower bounds <> 1 (e.g. 0 to 9 or 5 to 14), consider resizing the range using lbound and ubound.

Something like:

Code

Range("_MScenarioOccurence").Offset(0, 0).Resize(1, Ubound(ScenarioName())-Lbound(ScenarioName())).Value _
 = ScenarioName()

or:

Code

SizeR = Ubound(ValueImpactMatrix(),1) - Lbound(ValueImpactMatrix(),1)
     SizeC = Ubound(ValueImpactMatrix(),2) - Lbound(ValueImpactMatrix(),2)
     Range("_MOutputImpactV").Offset(1, 0).Resize(SizeR, SizeC).value = ValueImpactMatrix()

I wish you maximum inspiration and a clear head. It surely gave me some headaches to understand that and I am still not sure I totally grasped it :).
Cheers,
Dan.

Display More