Macros: Recorded Macro not working

Question

I have recorded a macro in excel using Record Macro option. However, when I run it, I get the Error 438 and the error box displays Object does not support property or method. Here is the generated macro code. Can someone explain here:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.Range("A1:C1").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$4:$C$4")
    ActiveChart.ChartType = xlPie
    Application.CutCopyMode = True
    Selection.Cut
    Sheets("Sheet3").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    ActiveCell.Offset(1, 0).Range("A1").Select

When I debug, I get the error in Selection.Cut. I am wondering how come a recorded macro generated not working code

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 11 '13 at 09:57

score 0 · Answer 1 · answered Nov 11 '13 at 10:11

0

Though not a good way,

but changing Selection.Cut to Selection.Parent.Parent.Cut should work.

And remember, avoid using Select whenever possible

answered Nov 11 '13 at 10:11

sam092

1,325
1
8
8

score 0 · Answer 2 · answered Nov 11 '13 at 12:59

Slightly polished version (using references and avoiding selection) that should work:

Sub Macro1()
' Macro1 Macro
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ActiveSheet   ' This is likely 'Sheet 1'
    Dim rng As Range
    Set rng = ActiveCell.Range("A1:C1")
    Dim shp As Shape
    Set shp = ws1.Shapes.AddChart
    shp.Chart.SetSourceData Source:=Range("'Sheet2'!$A$4:$C$4")
    shp.Chart.ChartType = xlPie
    Application.CutCopyMode = True
    shp.Cut
    Sheets("Sheet3").Paste
    Set ws2 = Sheets("Sheet2")
    ws2.Activate
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Macros: Recorded Macro not working

2 Answers2