naming a range of cells on a different spreadsheet

[slay0r]

Hi Guys,

Sorry for asking for so much help in the last couple of days. Doing a side project for work that I hope will get me some brownie points. At the same time it's a learning curve for me! I've just managed to fix something with Cindy so I Can open a side spreadsheet that's readonly. I need to name all the cells below as Import, I need to do the same for another tab and name it export as well later on. Am I doing it right below or have I gone a bit off to the side?

Sub nameimportlist21()
'
' nameimportlist21 Macro
' Macro recorded 12/07/2011 by jmartin
'
'
Windows("Credit-Stop List.xls").Activate
Range("A1:I240").Select
Application.Goto Reference:="import"
Windows("copytestmacromaster.xls").Activate
End Sub

 

[Damon Ostrander]

Hi slay0r,

It is not actually necessary to select or activate the windows, sheets, or ranges involved in order to name a range. In your case you can simply do this:

Sub nameimportlist21()
'
' nameimportlist21 Macro
' Macro recorded 12/07/2011 by jmartin
'
'
Workbooks("Credit-Stop List.xls").ActiveSheet.Range("A1:I240").Name = "import"

End Sub

You can also explicitly name the worksheet instead of having to have the sheet of interest active:

Workbooks("Credit-Stop List.xls").Worksheets("Sheet2").Range("A1:I240").Name = "import"

Keep Excelling.

Damon

 

[Jerry Sullivan]

Hi Jonathan,

You can add or redefine a named range with Workbook scope using the code below.
The code assumes Credit-Stop List.xls is already open.

Sub DefineName_Import()
    With Workbooks("Credit-Stop List.xls")
        .Names.Add ("Import"), _
            RefersTo:=.Sheets("Sheet1").Range("A1:I240")
    End With
End Sub

Your post didn't list the sheet name for the range - you can edit Sheet1 with your sheet's name.

Note that you don't have to Activate the window or workbook to modify it.

P.S. Oops. Sorry- I had this on my screen for a while and didn't realize Damon had responded.
(Fortunately for me my response wasn't too different)

 

[slay0r]

Thanks very much for the help guys. That's worked a treat. Didn't realise you could do it without activating so thank you very much for that tip too!

Kind Regards
Jonathan