Thanks Jacob Skaria the paste values is working now
I was hoping this would fix another issue I am having but it didn't. Once I
copy to sheet2 I want to save that sheet as a csv. The code creates an
archive copy (with timestamp suffix) and also a working copy (without time
stamp) that is uploaded via FTP. This is the code that I am using:

Dim strWksheet As String
Dim strPath As String
Dim strFileName As String
Dim strTimeStamp As String


strWksheet = "sheet2"
strPath = "mypath"
strFileName = "NewFile"
strTimeStamp = Format(Now(), "yyyy-mm-dd_hhmm")


Sheets(strWksheet).Copy
ActiveWorkbook.SaveAs Filename:=strPath & strFileName & _
strTimeStamp & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'close the new worksheet
ActiveWindow.Close
Application.DisplayAlerts = False
Sheets(strWksheet).Copy
ActiveWorkbook.SaveAs Filename:=strPath & Left(strFileName, 10) _
& ".txt", FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True
'close the new worksheet

Application.ScreenUpdating = True


exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: Copy_Data_Worksheet - " & Now()
GoTo exit_Sub


This works but is the filtered data has fewer lines of data on subsequent
runs there is an issue. The site I am uploading to requires that plank lines
be blank and as it is now the file uploads with commas separating the fields.
How can I clear the commas before upload?
Thanks