| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cut and Paste using Macro gives paste special method error
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut and Paste using Macro gives paste special method error
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").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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cut and Paste using Macro gives paste special method error
Dave,
Thanks for the reply, after some more reading, etc. It seems I have to change my code not to use active (ie. activesheet, workbook,etc), so I am going to visit that path. Cheers Lourens Dave Peterson wrote in message ... 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.) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste Special throwing an error | Excel Discussion (Misc queries) | |||
| Paste method error in macro | Excel Discussion (Misc queries) | |||
| Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
| VB Error: Paste Special method of range class failed | Excel Programming | |||
| Paste method of worksheet class failed error | Excel Programming | |||
Hybrid Mode
