How to generate CSV

[LEHoisveen]

Hello,
I have a order sheet where we type in orders from our customers.
It has some cells where we type in quantity for each product, now i want to "transfer" the cells wich have som contents in it to another sheet.

I need to create this csv file for importing into our accounting system. I are also using the excel sheet to create WayBills and labels for pallets.

IE.:

Customer: Test Company
CustomerId.: 123456
CustomerRefNr.: 1357
CustomerRefPerson: Test Testesen

Product 1: 3 pcs
Product 2: 1 pcs
Product 3: 0 pcs
Product 4: 4 pcs
Product 5: 0 pcs
Product 6: 2 pcs

I need to transfer it in this way:

1 Test Company; 123456;1357;Test Testesen;Product 1; 3
2 Test Company; 123456;1357;Test Testesen;Product 2; 1
3 Test Company; 123456;1357;Test Testesen;Product 4; 4
4 Test Company; 123456;1357;Test Testesen;Product 6; 2


Any ideas on how I can solve this?
I`m a noob in excel.

 

[onlyadrafter]

Hello,

Have made some assumptions, the data starts in cell A1 and the added data is in column B. Also, assumed that there is a sheet (tab) called Transfer. Have saved the file in C: titled test.csv

Sub CREATE_CSV()
    MY_CUST = Range("B1").Value
    MY_CUST_ID = Range("B2").Value
    MY_CUST_REF = Range("B3").Value
    MY_CUST_PER = Range("B4").Value
    MY_COUNT = 1
    For MY_ROWS = 5 To Range("A" & Rows.Count).End(xlUp).Row
        If Left(Range("A" & MY_ROWS).Value, 7) = "Product" Then
            MY_QTY = Replace(Range("B" & MY_ROWS).Value, " pcs", "")
            With Sheets("Transfer")
                .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
                    MY_COUNT & " " & MY_CUST & "; " & MY_CUST_ID & ";" _
                    & MY_CUST_REF & ";" & MY_CUST_PER & ";" & "Product " _
                    & MY_COUNT & "; " & MY_QTY
                MY_COUNT = MY_COUNT + 1
            End With
        End If
    Next MY_ROWS
    Sheets("Transfer").Copy
    ActiveWorkbook.SaveAs Filename:="C:\test.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
End Sub

Is this of any use?