Mid function does not work if parameter starts at 0 therefore if instr does
not find a space then the mid function falis.

Try running instr first and then test with if/then/else for greater than
zero and if it is greater than zero then run mid function.

You should be able to use code similar to your last row code to find extent
of data on source sheet. similar code works with columns.

Might also try:-

Set SourceRange = Sheets("we 9-8-07").UsedRange

regards,

OssieMac



"Nolaughmtr" wrote:

I get an error at " <<<<" when i try an run it again. basically im
trying to coppy information from one page to another and then splitting the
fist row on the second page. I would also like help on trying to add more
information from the 1st sheet to the second sheet. its not constant
information so im a bit confused on how to do it. If anyone wants to look at
the file please let me know because I know this is pretty confusing.

Sub copy_1()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("we 9-8-07").Range("F2:G93")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("DIE STATUS")
Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row

'With the information from the LastRow function we can
'create a destination cell and copy/paste the source range
Set DestRange = DestSheet.Range("A" & Lr + 1)
SourceRange.COPY DestRange

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Worksheets("DIE STATUS").Activate
cellCount = 2
With Worksheets("Die status")
Do While Cells(cellCount, "A") < ""

Number = Val(Cells(cellCount, "A"))
Text = Cells(cellCount, "A")
Text = Trim(Mid(Text, InStr(Text, " ")))<<<<
.Cells(cellCount, "A") = Number
.Cells(cellCount, "C") = Text
cellCount = cellCount + 1


Loop
End With




End Sub