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

I cannot recreate the issue you are mentioning...

When you try with the filter..the csv file generated will still have all
rows..isnt it?

--One option is to delete the unused rows (if that is within the used range)
before exporting to .csv
--OR try a row by row export to csv using code..

--
Jacob (MVP - Excel)


"Steve" wrote:

> 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

Steve, If Joel's suggestion doesnt work try row by row exporting....The below
code assumes that Column A is mandatory....Change to suit...


Dim intFile As Integer, strData As String, lngRow as Long, lngCol As Long

intFile = FreeFile
Open strFileName For Output As #intFile
lngRow = 1
lngCol = Cells(1, Columns.Count).End(xlToLeft).Column
Do While Range("A" & lngRow) <> ""
If Trim(Range("A" & lngRow)) <> "" Then
strData = Join(WorksheetFunction.Transpose(WorksheetFunction. _
Transpose(Range("A" & lngRow).Resize(, lngCol))), ",")
Print #intFile, strData
End If
lngRow = lngRow + 1
Loop
Close #intFile

--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

> I cannot recreate the issue you are mentioning...
>
> When you try with the filter..the csv file generated will still have all
> rows..isnt it?
>
> --One option is to delete the unused rows (if that is within the used range)
> before exporting to .csv
> --OR try a row by row export to csv using code..
>
> --
> Jacob (MVP - Excel)
>
>
> "Steve" wrote:
>
> > 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

This appears to be working
Thanks very much!

"joel" wrote:

>
> I can't guarentee my solution will work. Sometimes when data is written
> to a cell and then cleared excel still thinks the cell contains data. I
> delete the rows after the last row of data hoping this will solve your
> problems. The method I used to find the last row sometimes doesn't find
> the last row because a cell previously had data and you will get the
> same results you have now.
>
> 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
> with Activesheet
> LastRow = .range("A" & rows.count).end(xlup).row
> rows((LastRow + 1) & ":" & rows.count).delete
> end with
> 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
> with Activesheet
> LastRow = .range("A" & rows.count).end(xlup).row
> rows((LastRow + 1) & ":" & rows.count).delete
> end with
> 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
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199718
>
> http://www.thecodecage.com/forumz
>
> .
>