Range Select not working?!

dowingc

Board Regular
Joined
Oct 27, 2002
Messages
77
I have a very simple spreadsheet that is copying cell values from one workbook to another. At the start of the code I am trying to setup the initial activecell locations by using this code. however, it keeps erroring out but I don't know why. Help greatly appreciated! I am running this on Office XP, but it may run on O2K in the future....

Option Explicit
Public Sub macro1_Click()
Dim delay As Integer
Dim symbol, t As String
ChDir "c:\stock"
Sheets(1).Select
Range("A2").Select
Sheets(2).Select
Range("A2").Select <---------here is where i get the error "Selection method of Range class failed"
....


Thanks!

Chris
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Is A2 on sheet2 protected? Are there any objects on sheet2 that are active when you switch to it, i.e., if you look at sheet2, is there a chart that is active?
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Try this. I think your references might be the issue. This code should fix up any problems.

Option Explicit
Public Sub macro1_Click()
Dim WS1 as worksheet, WS2 as worksheet
Dim delay As Integer
Dim symbol, t As String
ChDir "c:\stock"

Sheets(1).activate
set WS1 =Activesheet
WS1.Range("A2").Select

Sheets(2).Activate
Set ws2= Activesheet
WS2.Range("A2").Select
 

dowingc

Board Regular
Joined
Oct 27, 2002
Messages
77
Not that I am aware of. I just put some labels (just text) on the top at A1:L1, thus having A2 be the first blank cell. I looked for protection and can't see any. It is obviously something with sheet2 though, I removed the line of code causing problems and another .select on sheet2 errored....frustrating...

Just read the post with code and testing it, seems to be working, thanks!!!

Chris
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,564
Messages
5,765,129
Members
425,262
Latest member
sabry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top