Sub Macro1()
Dim txt As String
Dim newsheet As Worksheet
Dim destination As Range
Dim MyArr
Dim TempArr
Dim TempVal As String
MyArr = ActiveSheet.UsedRange
TempArr = Application.Index(MyArr, 0, 2)
For i = 0 To UBound(TempArr)
TempVal = Application.Index(TempArr, i + 1)
'where error occurs TempVal = Mid(TempVal, 11, 3)
Cells(i + 1, 1).Value = TempVal
Next i
Set newsheet = ActiveWorkbook.Worksheets.Add
Columns(2).NumberFormat = "@"
Set destination = newsheet.Range("a1")
destination.Resize(UBound(MyArr, 1), UBound(MyArr, 2)).Value = MyArr
Set destination = newsheet.Range("k1")
destination.Resize(UBound(TempArr, 1), UBound(TempArr, 2)).Value = TempArr
End Sub

so i'm trying to use arrays to do data manipulation and i am running into problems. i've directly tried to declair tempvar as string to do text data manipluation, integer/long/etc to do number manipluation, and nothing seems to work. help please :-)