It's ok Dave thanks. I checked through threads on here and found the following code that seems to work.
It produces a txt file instead of a csv and I'd prefer it to go on the desktop rather than into the Temp folder; so if you have any amendments to achieve those ends I'd be very grateful.
Private Sub CommandButton21_Click()
Dim rng As Range, lRow As Long
Dim stOutput As String, stNextLine As String, stSeparator As String
Dim stFilename As String, stEncoding As String
Dim fso As Object
'-------------------------------------------------------------------------------------
'CHANGE THESE PARAMETERS TO SUIT
Set rng = ActiveSheet.UsedRange 'this is the range which will be written to text file
stFilename = "C:\Temp\Upload.txt" 'this is the text file path / name
stSeparator = "|" 'vbTab 'e.g. for comma seperated value, change this to ","
stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"
'-------------------------------------------------------------------------------------
For lRow = 1 To rng.Rows.Count
If rng.Columns.Count = 1 Then
stNextLine = rng.Rows(lRow).Value
Else
stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
End If
If stOutput = "" Then
stOutput = stNextLine
Else
stOutput = stOutput & vbCrLf & stNextLine
End If
Next lRow
Set fso = CreateObject("ADODB.Stream")
With fso
.Type = 2
.Charset = stEncoding
.Open
.WriteText stOutput
.SaveToFile stFilename, 2
End With
Set fso = Nothing
End Sub
Bookmarks