Try this from Tom Ogilvy:

Sub CreateFile()
Dim sh as Worksheet, sh1 as Worksheet
set sh = Activesheet
workbooks.Add Template:=xlWBATWorksheet
set sh1 = Activesheet
sh.Range("A1:F20").Copy
sh1.Range("A1").PasteSpecial xlValues
Application.DisplayAlerts = False
sh1.parent.SaveAs "C:\Myfolder\Myfile.txt", xlCSV '<== Change
Application.DisplayAlerts = True
End Sub

or this from NickHK:

Private Sub CommandButton1_Click()
Dim DatObj As DataObject
Dim FileNum As Long
Dim TempStr As String

Const TEXTFILE As String = "C:\routes.txt"
Const TEXTFORMAT As Long = 1

Range("A1:F20").Copy

Set DatObj = New DataObject
With DatObj
.GetFromClipboard
If .GetFormat(TEXTFORMAT) = True Then
FileNum = FreeFile
TempStr = .GetText(TEXTFORMAT)
'Seem to get an extra vbCrLf from the DataObject, so remove
TempStr = Left(TempStr, Len(TempStr) - 2)

Open TEXTFILE For Output As #FileNum
Print #FileNum, TempStr
Close #FileNum
End If
End With

Application.CutCopyMode = False

End Sub





On Oct 4, 10:40 am, Joe K. <Joe wrote:
I have a spreadsheet with a worksheet that I need to create a simple macro
to copy the contents from cells a1 to g100 to a csv file format.

Please help me with this macro.

Thanks,