How to copy cells from excel to one long string

Question

I tried to make some simple excel makro for copying id. nr. of our items to our company ERP system. I dont know how to copy data to one string, so ERP system can find all of items. My makro makes everything what I wanted, but it copy cells under.

Here is example what i want:

7042151,7042152,7042153,7042154,7042145,7042155,7025449,3012928,3006999,3002768,3002761,3002768,3010873,3008762,3001228,3002761,3760114

And here is example what i got from makro.

7042151,
7042152,
7042153,
7042154,
7042145,
7042155,
7025449,
3012928,
3006999,
3002768,
3002761,
3002768,
3010873,
3008762,
3001228,
3002761,
3760114

Here is my code:

Sub copytext()
    Dim txt As Worksheet
    Dim rng As Range
    Dim Last_Col As Long
    Dim LastRow As Integer
    Set rng = Application.Selection
    Application.Workbooks.Add
    Set txt = Application.ActiveSheet
    rng.Copy
    Application.ActiveSheet.Range("A1").PasteSpecial xlPasteValues
    With Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(-1, 0))
      .Value = Evaluate(Replace("if(@<>"""",@&"","")", "@", .Address))
    End With
    Range("A1").NumberFormat = "@"
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Range("A1:A" & LastRow).Select
    Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))
    Selection.Copy
End Sub

It should work like you highlight cells with data, click on makro, and makro make copy in right format to clipboard.

I tried this after Selection.Copy but it was same.

    Dim objData As New MSForms.DataObject
    Dim strText As String
    objData.GetFromClipboard
    strText = objData.GetText
    objData.SetText strText
    objData.PutInClipboard

Answer 1

This code set the new workbook activesheet cell A1 to the generated value from the Application.Selection property, and paste it to the clipboard.

Sub copytext()
    Dim txt As Worksheet
    Dim rng As Range
    Dim Last_Col As Long
    Dim LastRow As Integer
    Set rng = Application.Selection
    Application.Workbooks.Add
    'Set txt = Application.ActiveSheet   not used
    Dim outtext As String
    For Each cel In rng
      If outtext = "" Then
      outtext = cel.Value
      Else
      outtext = outtext & "," & cel.Value
      End If
    Next cel
    
    Range("A1").NumberFormat = "@"
    Range("A1") = outtext
    'LastRow = ActiveSheet.UsedRange.Rows.Count
    'ActiveSheet.Range("A1:A" & LastRow).Select
    Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))
    Selection.Copy
End Sub