Cell reference as variable?

[Magriza]

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:

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!

 

[hayati]

Hi Magriza,

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

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]

How about this?

Dim i As String 
i = ActiveCell.Address

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

 

[Magriza]

Thank you to both of you! Worked perfectly. Had to select sheet2 before the range in the second solution before it worked...

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!

 

[hayati]

Briliant

Thanks Norie,

Learning something new everyday!
Cheers,
Matt

 

[Norie]

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]

Sorry, didn't really explain myself well. The second part of your 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!

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

Thanks once again for your help.

 

[just_jon]

Sorry, didn't really explain myself well. The second part of your 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!

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