Hi,

I have noticed a coment from Tom Ogilvy on some elses code at the
following site :http://www.mcse.ms/archive161-2004-1-345300.html

and I have modified the bit where I open the document "monthly report
statistics.xls" to the following :

++++++++++++++++++++++
Range("E2:F2").Resize(r).Copy

Workbooks.Open Filename:= _
"c:\temp\monthly report statistics.xls"

For i = 1 To 12 Step 1

Select Case myMonth

Case Is = "01"
Workbooks("monthly report
statistics.xls").Sheets("Sheet1").Range("A2").Past eSpecial
xlPasteValues
Selection.NumberFormat = "h:mm"

Case Is = "02"
++++++++++++++++
I still get the pastespecial error, I have also changed some
references to not select/activate the document as I have read that it
can also cause the error.

Cheers
Lourens

(Lourens Pentz) wrote in message . com...
Hi,

I have an email that is saved to a text file and then I run an excel
macro to format the data and apply formulas where needed. At the end I
copy the last 2 columns to a seperate existing excel doc, where I will
then create charts.
Now my issues come in when I copy the last two columns to transfer it
to the chart document. Digging around on the web make it seem that the
data is not saved on the clipboard when trying to paste to the chart
document giving the Method Error. I can follow the macro copying and
pasting the data, so it must be visible on the clipboard, the error
actually appears after the past action.

I get the "method error" when using range and I get the "object does
not support this property" when using activecells (see further in the
case statement).

This is mostly recorded macro modified where needed, I use WinXP pro
and Office 2003.


Can anybody shed any light on this ?

Cheers
Lourens
++++++++++++++++++++++++++++++++++++++
Sub Monthly_statistics()
'
' stats Macro
' Macro recorded
'
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector.CurrentItem

Dateparm = Year(Date) & Month(Date) & Day(Date)
myMonth = Right(myItem.Subject, 2)

Workbooks.OpenText Filename:= _
"C:\temp\" & myItem.Subject & ".txt", _
Origin:=xlMSDOS, StartRow:=5, DataType:=xlFixedWidth,
FieldInfo:=Array( _
Array(0, 4), Array(2, 9), Array(3, 1), Array(9, 4), Array(11,
9), Array(12, 1), Array(18, 1) _
), TrailingMinusNumbers:=True


Range("A1").Select
ActiveCell.FormulaR1C1 = "Start Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("C1").Select
ActiveCell.FormulaR1C1 = "End Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "End Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Total time"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Average Time"

Lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

r = Lastrow
Col = 1


For i = 1 To r Step 1

Select Case ActiveSheet.Cells(i, Col).Value

Case Is = "AP"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (previous
day)"

Case Is = "AC"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "findAYCEusers (current day)"

Case Is = "RA"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Radius2Arbor"

Case Is = "AO"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "AYCEoverlaps"

Case Is = "CD"
ActiveSheet.Cells(i, Col).Value = ""
Rows(i + 1).Value = ""
ActiveSheet.Cells(i + 1, Col).Select
ActiveCell.FormulaR1C1 = "Daily_CDR_DATA_Arch"

End Select
Next i



'populate the "E" column with the formula to calculate the total
'time of execution.

cnt = -2
Col = 5
For i = 3 To r Step 1
If ActiveSheet.Cells(i, Col - 1).Value < "" Then
ActiveSheet.Cells(i, Col).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]RC[-1],RC[-1]+1-RC[-3],RC[-1]-RC[-3])"
ActiveSheet.Cells(i, Col).NumberFormat = "h:mm"
Else
If ActiveSheet.Cells(i - 1, Col - 1).Value < "" And _
ActiveSheet.Cells(i, Col - 1).Value = "" Then
ActiveSheet.Cells(i - 1, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i - 1, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
End If
If i = r Then
ActiveSheet.Cells(i, Col + 1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
"]C[-1]:RC[-1])"
ActiveSheet.Cells(i, Col + 1).NumberFormat = "h:mm"
cnt = -2
End If
cnt = cnt + 1
Next i


ActiveWorkbook.SaveAs Filename:= _
"c:\temp\monthly " & myItem.Subject & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Range("E2:F2").Select
Selection.Resize(r).Select
Selection.Copy



Workbooks.Open Filename:= _
"c:\temp\monthly report statistics.xls"




For i = 1 To 12 Step 1

Select Case myMonth

Case Is = "01"


Range("A2").Select
Range.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "02"
ActiveSheet.Cell(2, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "03"
ActiveSheet.Cells(2, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "04"
ActiveSheet.Cells(2, 7).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "05"
ActiveSheet.Cells(2, 9).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "06"
ActiveSheet.Cells(2, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "07"
ActiveSheet.Cells(2, 13).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "08"
ActiveSheet.Cells(2, 15).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "09"
ActiveSheet.Cells(2, 17).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "10"
ActiveSheet.Cells(2, 19).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "11"
ActiveSheet.Cells(2, 21).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"

Case Is = "12"
ActiveSheet.Cells(2, 23).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Select
Next i

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "h:mm"


End Sub