Creating a new sheet with data that is merged to one cell per row

[coretex99]

Hello,

I've been struggling with what I thought would be a basic Macro, would appreciate some guidance if possible.

Our CRM spits out an excel sheet of hundreds of rows similar to the one below.

Unique Item Number

Item

Item Description

Code

Quantity

Units

Value

Currency

Weight

Weight 2

Country

Reference Type

Details

VAT

12345​

GG666

Test Item

5555

1​

EA

99.99​

GBP

5​

KR

I need to create a Macro on a separate "template" sheet that can take all the data per row and create a new sheet with it all merge in the first row, and subsequent rows, delaminated with a comma.

12345,Test Item ,GG666 ,1,EA,99.99,GBP,5,,KR,,,,

I have tried recording a Macro but not getting very far. Any help would be appreciated Thanks

 

[Legacy 143009]

Sub copyData()
  Sheets.Add.Name = "template"
  Dim lRow As Integer
    lRow = Worksheets("CRM").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lRow
          With Worksheets("CRM")
               Worksheets("template").Cells(i, 1).Value = .Cells(i,1).Value&","&.Cells(i,3).Value&","&.Cells(i,2).Value&","&.Cells(i,5).Value&","&.Cells(i,6).Value&","&.Cells(i,7).Value&","&.Cells(i,8).Value&","&.Cells(i,9).Value&","&.Cells(i,10).Value&","&.Cells(i,11).Value&","&.Cells(i,12).Value&","&.Cells(i,13).Value&","&.Cells(i,14).Value
          End With
    Next
End Sub