Cell reference as variable?

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
Does anyone know if it's possible to set the active cell on one sheet as a variable so that it can be used to select the same cell on another sheet? So that if I select, say, C5 on Sheet1 and then run the macro it will automatically select Sheet2 and cell C5? Both cells would contain no data. I know I can do it if I have the cell reference typed into the cell:
Code:
Dim i As String
i = ActiveCell

Sheets("Sheet2").Activate
Range(i).Select

However, I am unable to see how I can get it to work if the cell on Sheet1 is blank.

I'm sure it's simple but so am I so please help! :confused:
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

hayati

Board Regular
Joined
Oct 3, 2005
Messages
118
Hi Magriza,

Welcome to the Board. With my limited knowladge i used this in my code to do the trick:

Code:
Sub activate()
Dim i
Dim R
Dim C
i = ActiveCell
R = ActiveCell.Row
C = ActiveCell.Column
Sheets("Sheet2").Select
Cells(R, C).Select
End Sub

I am sure there is a better way to do it but I couldn't find it at the time

Cheers,
Matt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
How about this?
Code:
Dim i As String 
i = ActiveCell.Address

Sheets("Sheet2").Range(i).Select
 

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
Thank you to both of you! Worked perfectly. Had to select sheet2 before the range in the second solution before it worked...

Code:
Dim i As String
i = ActiveCell.Address

Sheets("Sheet2").Select
Range(i).Select

I'm going to sacrifice a floppy disc on my mouse mat altar in thanks! :biggrin:
 

hayati

Board Regular
Joined
Oct 3, 2005
Messages
118

ADVERTISEMENT

Briliant

Thanks Norie,

Learning something new everyday!
Cheers,
Matt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Magriza said:
Had to select sheet2 before the range in the second solution before it worked...
What are you actually trying to do?

You shouldn't need to select anything to work with a range/cell.
 

Magriza

Well-known Member
Joined
Nov 16, 2005
Messages
508
Sorry, didn't really explain myself well. The second part of your code:

Code:
Sheets("Sheet2").Range(i).Select

Returned the following error:

Run-time error '1004':

Select method of Range class failed.

However, as soon as I instructed the code to select sheet2 first it worked fine!
Code:
Sheets("Sheet2").Select
Range(i).Select

Thanks once again for your help.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Magriza said:
Sorry, didn't really explain myself well. The second part of your code:

Code:
Sheets("Sheet2").Range(i).Select

Returned the following error:

Run-time error '1004':

Select method of Range class failed.

However, as soon as I instructed the code to select sheet2 first it worked fine!
Code:
Sheets("Sheet2").Select
Range(i).Select

Thanks once again for your help.

You cannot select both the sheet and range in the same Select statement. But you can probably avoid selects altogether, as has been mentioned...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,080
Members
412,566
Latest member
TexasTony
Top