Excel - How do I send data in a form template to a summary table

[AntEvans82]

Hi Guys,

I have a sales order for that I am asking field agents to complete and send back to my processing team within head office. The form itself is relatively simple and has some data validation and lookup functions to assist in the completion for the field agents when they are with potential clients.

I have written code to save a copy of the form as a separate workbook (and that felt complicated enough,) but I have started writing code to export the data (Date, Order Number, Quantity, Description, Product ID, Size, Price, Total) and export it to a 'Summary Table.'

At the moment the summary is on a separate sheet within the workbook, but ideally I would love for VB to open a named workbook and export the data into this - taking into account that that there may be data already in there and put it into the next available blank line. I have started by using the Offset function, but I seem to be getting 'Type Mismatch' errors and I have no idea why.

The Code I Have used is as follows:

Sub Send_To_Summary()

Dim SOdate As Date
Dim Order As String
Dim Qty As Double
Dim Description As Double
Dim code As String
Dim size As String
Dim price As Double
Dim total As Double

Worksheets("Sales_Order_Form").Activate

SOdate = Range("H4").Value
Order = Range("D11").Value
Qty = Range("C24:C39").Value
Description = Range("D2439").Value
code = Range("E24:E39").Value
size = Range("F24:F39").Value
price = Range("G24:G39").Value
total = Range("H24:H39").Value

Worksheets("Order_Summary").Activate
Range("A2").Activate

Do

If ActiveCell.Value = "" Then Exit Do
ActiveCell.Offset(1, 0).Activate

Loop

ActiveCell.Value = Date
ActiveCell.Offset(0, 1).Value = Order
ActiveCell.Offset(0, 2).Value = Qty
ActiveCell.Offset(0, 3).Value = Description
ActiveCell.Offset(0, 4).Value = code
ActiveCell.Offset(0, 5).Value = size
ActiveCell.Offset(0, 6).Value = price
ActiveCell.Offset(0, 7).Value = total

End Sub

I would really appreciate help with this one - even if the summary data remains in a sheet within the same workbook as the form.

Maybe this is a case of running before I have learnt to walk, but this would streamline my operations greatly and would be a far more professional way of completing orders etc.

Please Help

Best wishes,

Ant

[westconn1]

but I seem to be getting 'Type Mismatch' errors and I have no idea why.

neither have we, but we might if we had some idea on which lines you get this error

it is not much harder at all to write to a different workbook, but may as well get this part working first

edit: on second thoughts i can see the error, you are trying to assign an array (range of multiple cells) to a single value variable of type string or type double, you will have similar problems assigning array variables to single cells in the summary sheet

you need to be more clear on what you want to do

try this to see if it does what you want

Set sht = ActiveSheet
Set src = Sheets("Sales_Order_Form")
Set dest = Workbooks.Open("path\workbookname").Sheets("Order_Summary")
nextrow = dest.Range("a65535").End(xlUp).Row + 1    'get next empty row
dest.Cells(nextrow, 1) = src.Range("h4")
dest.Cells(nextrow, 2) = src.Range("d11")
dest.Range(dest.Cells(nextrow, 3), dest.Cells(nextrow + 16, 8)) = src.Range("c24:h39")
dest.Parent.Save
dest.Parent.Close   ' optional

untested