Excel - How do I send data in a form template to a summary table
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("D24:D39").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
Re: Excel - How do I send data in a form template to a summary table
Quote:
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
vb Code:
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
