Use address of named range to find same address in another worksheet

**dwsteyl** · 06-08-2013, 02:53 PM

Hi

Let's assume I have Sheet1. On it I've named cell A5 as "MyRange". I want to jump to MyRange using its name, find and put its address (A5) in a variable, and then jump to Sheet2 and select the same cell address (A5) on Sheet2 by using the address assigned to the variable.

I've been at this for an hour or so, but when I use something like ActiveSheet.Cells(MyRange).Select it jumps back to Sheet1(A5) instead of selecting A5 on Sheet2.

Thanks!

**Jakobshavn** · 06-08-2013, 03:22 PM

In two steps:

Sub dural()
Sheets("Sheet5").Activate
Range(Range("MyRange").Address).Select
End Sub

**dwsteyl** · 06-08-2013, 03:55 PM

Jacobshavn

Thank you for the reply. I've tested the code given, but it still jumps back to the previous sheet.

Let me be more precise with my problem. I'm actually jumping between workbooks. Here is a simplified example of my code:

Dim OldBook As String
Dim NewBook As String
Dim ControlPoint As String

'both workbooks are already open
OldBook = "MyOldBook.xlsm"
NewBook = "MyNewBook.xlsm"

Workbooks(OldBook).Activate 

Range("MyRange").Select   '<-this is cell A5, named as "MyRange" on Sheet1 in OldBook
ControlPoint = ActiveCell.Address '<-could this be done differently?
 
Workbooks(NewBook).Activate
Sheets.Add.Name = "NewSheet"
Sheets("NewSheet").Activate
Range(Range("MyRange").Address).Select

After this last line it still selects "MyRange" in OldBook, instead of the same address in NewBook, even though NewBook is the active book.

The reason I want need to do this is because I need to give the same range name to cell A5 in NewBook. I therefore need to select it using code. Once I'm on it it's easy to name it.

Thank you for your help!

**Jakobshavn** · 06-08-2013, 04:22 PM

Try the following:

Range(Workbooks(OldBook).Range("MyRange").Address).Select

**dwsteyl** · 06-08-2013, 04:56 PM

Hi

I don't know why, but this works:

Dim OldBook As String
Dim NewBook As String
Dim ControlPoint As String

'both workbooks are already open
OldBook = "OldBook.xlsm"
NewBook = "NewBook.xlsm"

Workbooks(OldBook).Activate

Range("MyRange").Select   '<-this is cell A5, named as "MyRange" on Sheet1 in OldBook
ControlPoint = ActiveCell.Address '<-could this be done differently?
 MsgBox ControlPoint
 'Exit Sub
 
Workbooks(NewBook).Activate
Sheets.Add.Name = "NewSheet"
Sheets("NewSheet").Activate
Range(ControlPoint).Activate

Thank you for the suggenstions!