Command Prompt = Crashed Macro

[jkelly228]

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]

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.

Please Login or Register  to view this content.

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]

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.

Please Login or Register  to view this content.