Re: Cut and Paste using Macro gives paste special method error

From: Dave Peterson (ec35720_at_netscapeXSPAM.com)
Date: 11/16/04 

Date: Mon, 15 Nov 2004 18:30:40 -0600

Maybe you could add some:
        msgbox application.cutcopymode
throughout your code.

My guess is that you're copying too soon.

I think I'd try moving the .copy command after the workbooks.open line and after
all the stuff you do to the headers.

Maybe right before this line:
     For i = 1 To r Step 1

But that's an untested guess.

If worse came to worse, you could always copy right before you paste for each
one (but test just moving it to see if that fixes it first.)

Lourens Pentz wrote:
>
> 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").PasteSpecial
> 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
>
> pentzol@hotmail.com (Lourens Pentz) wrote in message news:<4baec153.0411091819.3300bb58@posting.google.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 = Active***.UsedRange.Row - 1 + _
> > Active***.UsedRange.Rows.Count
> >
> > r = Lastrow
> > Col = 1
> >
> >
> > For i = 1 To r Step 1
> >
> > Select Case Active***.Cells(i, Col).Value
> >
> > Case Is = "AP"
> > Active***.Cells(i, Col).Value = ""
> > Rows(i + 1).Value = ""
> > Active***.Cells(i + 1, Col).Select
> > ActiveCell.FormulaR1C1 = "findAYCEusers (previous
> > day)"
> >
> > Case Is = "AC"
> > Active***.Cells(i, Col).Value = ""
> > Rows(i + 1).Value = ""
> > Active***.Cells(i + 1, Col).Select
> > ActiveCell.FormulaR1C1 = "findAYCEusers (current day)"
> >
> > Case Is = "RA"
> > Active***.Cells(i, Col).Value = ""
> > Rows(i + 1).Value = ""
> > Active***.Cells(i + 1, Col).Select
> > ActiveCell.FormulaR1C1 = "Radius2Arbor"
> >
> > Case Is = "AO"
> > Active***.Cells(i, Col).Value = ""
> > Rows(i + 1).Value = ""
> > Active***.Cells(i + 1, Col).Select
> > ActiveCell.FormulaR1C1 = "AYCEoverlaps"
> >
> > Case Is = "CD"
> > Active***.Cells(i, Col).Value = ""
> > Rows(i + 1).Value = ""
> > Active***.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 Active***.Cells(i, Col - 1).Value <> "" Then
> > Active***.Cells(i, Col).Select
> > ActiveCell.FormulaR1C1 = _
> > "=IF(RC[-3]>RC[-1],RC[-1]+1-RC[-3],RC[-1]-RC[-3])"
> > Active***.Cells(i, Col).NumberFormat = "h:mm"
> > Else
> > If Active***.Cells(i - 1, Col - 1).Value <> "" And _
> > Active***.Cells(i, Col - 1).Value = "" Then
> > Active***.Cells(i - 1, Col + 1).Select
> > ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
> > "]C[-1]:RC[-1])"
> > Active***.Cells(i - 1, Col + 1).NumberFormat = "h:mm"
> > cnt = -2
> > End If
> > End If
> > If i = r Then
> > Active***.Cells(i, Col + 1).Select
> > ActiveCell.FormulaR1C1 = "=AVERAGE(R[" & -cnt &
> > "]C[-1]:RC[-1])"
> > Active***.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"
> > Active***.Cell(2, 3).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "03"
> > Active***.Cells(2, 5).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "04"
> > Active***.Cells(2, 7).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "05"
> > Active***.Cells(2, 9).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "06"
> > Active***.Cells(2, 11).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "07"
> > Active***.Cells(2, 13).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "08"
> > Active***.Cells(2, 15).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "09"
> > Active***.Cells(2, 17).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "10"
> > Active***.Cells(2, 19).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "11"
> > Active***.Cells(2, 21).Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Selection.NumberFormat = "h:mm"
> >
> > Case Is = "12"
> > Active***.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 

-- 
Dave Peterson