Run Time 1004 error on simple macro

[sprucethebruce]

Private Sub CommandButton1_Click()
'
' Macro9 Macro
' Macro recorded 2/09/2011 by
'

'
    ActiveWindow.SmallScroll Down:=-21
    Range("AG7:AG29").Select
    Selection.Copy
    Sheets("Analysis").Select
    Range("B9").Select
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N44").Select
    Sheets("Machine Shop").Select
End Sub

I keep getting a runtime error 1004, select method of range class failed. This was a simple recorded macro, I've done plenty like this before with no problem, all of a sudden I can't get it to work.

To explain what I want to do, I want to take the info from column AG7:AG29 on worksheet 'Machine Shop', then move to worksheet titled 'analysis', select cell A9 (this is where the run time error keeps occuring), then find next blank column and paste the information from the column I'd copied in worksheet 'Machine Shop', into this column.

 

[Norie]

Try this.

Worksheets("Machine Shop").Range("AG7:AG29").Copy
 
Worksheets("Analysis").Range("A9").End(xlDown).Offset(1).PasteSpecial  Paste:=xlPasteValues

 

[Michael M]

Is there a blank column within the data set ?, or do you want the next blank column to the right of the data set ??

 

[sprucethebruce]

Next blank column to the right of the dataset Michael.

 

[sprucethebruce]

Norie, it kind of works but its finding the next column down, I want it to find the next blank column to the right.

I do admire the simplicity of these things when you know what you're doing. Looks a far tidier piece of code than what the recorder generates.

 

[Norie]

Didn't realise it was blank column.

Worksheets("Machine Shop").Range("AG7:AG29").Copy
 
Worksheets("Analysis").Cells(9, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial Paste:=xlPasteValues

<!-- / message --><!-- sig -->