Command Prompt = Crashed Macro

**jkelly228** · 07-12-2011, 06:24 PM

Hello All,

I have a very simple macro that takes data, does a simple pivot and then copy's and pastes the data as values to another sheet. I have tons of macro's that do the same thing. However, I decided to get fancy and put a command button that would let users kick off the macro at the click of a button.

However, after I did this the macro now crashes whenever I use the command "Cells.Select". This needs to be done in order to copy the pivot page and paste the values on another sheet.

The error that I get is: Select Method of Range class failed

I have used this command a hundred times without issue, so not sure what is going on now. This all started when I put in a command button. Does anyone know why this would happen and the workaround for it?

I appreciate any help that can be provided.

Kelly

**Paul** · 07-12-2011, 08:33 PM

Hi Kelly,

When you use a commandbutton to trigger a macro, the button itself usually takes focus. This may or may not be the issue, as we don't see any of your code or a sample workbook.

Also, whenever you are working with multiple worksheets and workbooks, ALWAYS ALWAYS ALWAYS preface any range argument with the fully qualified parent object reference.

For example, instead of Cells.Select, try Sheets("Sheet1").Range("A1:G20").Select. If you have to reference Sheets("Sheet1") many times, use a With/EndWith clause.

With Sheets("Sheet1")
    .Range("A1:A10").Copy Sheets("Sheet2").Range("A1")
    .Range("B1:B10").Copy Sheets("Sheet3").Range("B1")
End With

Also, it is rarely necessary to select any cells to actually perform a task. If you do a lot of select and activate statements, it will tend to slow down a macro (especially within loops).

**jkelly228** · 07-13-2011, 11:38 AM

Hi Paul,

Thank you for the help. Your recommendation has worked to an extent, but I still come up with errors. Below is a piece of the code that is causing the most problems. The two lines that are commented out (with the ' mark) are what are giving me the problems.

I appreciate any help you can provide. Thank you.

    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    Sheets("Sheet1").Range("A1:K12000").Select
    Selection.Copy
'    Sheets("Sheet2").Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
'    Sheets("Sheet2").Range.Cells(1, 1).Resize(Columns(2).Find("Item", , xlFormulas, xlPart).Row - 1).EntireRow.Delete
    LastRow2 = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    Sheets("Sheet2").Range("A2:K" & LastRow2).Select
    Selection.Copy
    
    Workbooks.Open Filename:="N:\Supply_Chain\Demand_Plan\ASCP\Master Grid_Macro.xlsm"
    Windows("Master Grid_Macro").Activate
    Sheets("ASCP_Raw").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues