Select method of Range class failed

Asked By jamyjolts 1180 points N/A Posted on - 06/26/2011

Hi,

I am at work and I am using Microsoft  Excel 2003 with  Windows XP Home Edition. I am working with some macros that will select some range of cells from a worksheet and unlock since they are locked and will copy its contents and go to another worksheet to paste the contents and re-locke again the open worksheet.

When I loaded the macro it works great. I have a button that I want to paste the macro in it. When I tried, I met an error. I pasted the screen shot below.

Run-time error '1004':

Select method of Range class failed

For those who tried this error message and solved the problem, please share some ideas on how to get rid of the error. Thanks.

• Status: Open
• Question Views: 1797
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Demna

Go To Solution

Best Answer

Best Answer

Answered By Demna 0 points N/A #124943

Select method of Range class failed

Hi Jamyjolts,

This problem can occur when you give the workbook a defined name and then copy the worksheet several times without first saving and closing the workbook. If this error message shows. Here are two solutions you can try.

Retype the sheets command.

Put the 'Sheets("sheetname").' code right before the 'Range('A1:D50").Select' code. Sample, for :
Sheets(Work1").Range("A1:D50").Select

Type:
Sheets("Work1").Select
Sheets("Work1").Range("A1:D50").Select
By doing this, the sheet is selected before running any other range or sheet type command and that corrects the error.

Save and close the workbook periodically while the copy process is occurring.

Instead of copying an existing worksheet, insert a new worksheet from a template. Follow the steps below:

1. Create a new workbook. Leave only one work sheet. Delete all other worksheet on that work book.
2. Format that workbook and encode details on your workbook such as charts, data and text.
3. On the upper left corner Click on “File” then click “Save As”.
4. Type the name of your Excel Template inside the file name box.
5. Choose “Template (*.xlt) in the “Save as type list” then click on “Save”.
6. “Sheets. Add Type:=pathfilename” use this code to programmatically insert the template. pathfilename this string in this code contains the full path and file name for your sheet template.

Cheers!

About Demna

Questions
0

Answers
6

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 06/30/2011
• Question Category: MS Excel