Prev: range loop? help
Next: Getting Data Validation to execute when using Paste Special...
From: Steve on 30 Apr 2010 13:07
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/showthread.php?t=199718
>
> http://www.thecodecage.com/forumz
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: range loop? help
Next: Getting Data Validation to execute when using Paste Special...