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.
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!
