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