VBA - referring to a named range on a different sheet

[Aclare87]

Hi, I just named a range on a sheet and would like to paste that range on a different worksheet within the same workbook. I can't figure out how to refer to the range. I've tried several different variations so far. Example below:

ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"

'Add new sheet and paste data
ActiveSheet.Name = "Raw_data_Sector_Summary"
ActiveWorkbook.Sheets.Add.Name = "RawData"
Range("A1").Formula = "=RawData!(RawSectorData)"

Anyone??

Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

What's the name of the worksheet to which the name was added? The syntax is:

Range("A1").Formula = "=SheetName!RawSectorData"

But a couple of points:

1. Why do you need to create a sheet level name rather than a workbook level name?
2. The formula will return an error because it refers to an array.

 

[Aclare87]

nevermind, got it to work.

'Name data as range
ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
'Add new sheet and paste data
ActiveWorkbook.Sheets.Add.Name = "SectorTop"
ActiveWorkbook.Sheets("RawData").Range("RawSectorData").Copy Destination:=Range("A1")

 

[Aclare87]

How do you name something on a workbook level versus a worksheet level? (new to VBA. just started teaching myself like a month ago). Thanks for the help!

 

[Andrew Poulsom]

One way:

ActiveSheet.Range("A6:K29").Name = "RawSectorData"

 

[Aclare87]

Great, thanks for the tip!