Hello,

I need some help debugging my code.
From an opened excel spreadsheet I would like to determine the last row
that has data in it and copy that data to a new workbook.

The error I am receiving is in the code to copy and is as follows:
Run-time error ‘1004’: Application defined or object defined error.

Thanks for your time and advise.

Ron


Private Sub SaveData_Click()

Dim ExcelLastCell As Object
Dim LastRowWithData As String
Dim Row As String
Dim NewBook As Object


' Find last cell with data in starting at cell A10

Range("A10").Select

Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

' Determine the last row with data in it

LastRowWithData = ExcelLastCell.Row
Row = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row < 1
Row = Row - 1
Loop
LastRowWithData = Row

' Create new workbook

Set NewBook = Workbooks.Add
With NewBook
..SaveAs Filename:="new.xls"
End With

' Copy selected cells from original workbook sheet 5 to sheet 1 of the
new workbook

Workbooks("original.xls").Worksheets("sheet
5").Range("A10:LastRowWithData").Copy _
Destination:=Workbooks("new.xls").Worksheets("Shee t1").Range("A1")

End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/