Ok, so here's what I am trying to do. I am trying to use a variable sheet name as a Named Range in my procedure but it is not working.

Here's the part of my code that is failing:

ActiveWorkbook.Names.Add Name:=NameRange1, RefersToR1C1:= _
"=OFFSET(DataSheet1!R1C1,0,0,COUNTA(DataSheet1!C1) ,COUNTA(DataSheet1!R1))"
ActiveWorkbook.Names(NameRange1).Comment = ""


I have declared DataSheet1 as a String that reads like this:

date_1 = Format(Date - 1, "mm.dd.yy")
DataSheet1 = date_1 & " Data"


I've tried to put single quotes around DataSheet1 in the Offset formula but that fails as well:

ActiveWorkbook.Names.Add Name:=NameRange1, RefersToR1C1:= _
"=OFFSET('DataSheet1'!R1C1,0,0,COUNTA('DataSheet1' !C1),COUNTA('DataSheet1'!R1))"
ActiveWorkbook.Names(NameRange1).Comment = ""

Any ideas?

If the DataSheet1 part of the string is separate vba variable, you must reference it separately and concatenate it into the string you're building. You'll definitely need those quote in there, too.

RefersToR1C1:= "=OFFSET('" & DataSheet1 & "'!R1C1, 0, 0, COUNTA('" & DataSheet1 & "'!C1), COUNTA('" & DataSheet1 & "'!R1))"


NOTE:

DataSheet1 = Format(Date - 1, "mm.dd.yy") & " Data"

Quote:

---

Originally Posted by JBeaucaire

If the DataSheet1 part of the string is separate vba variable, you must reference it separately and concatenate it into the string you're building. You'll definitely need those quote in there, too.

RefersToR1C1:= "=OFFSET('" & DataSheet1 & "'!R1C1, 0, 0, COUNTA('" & DataSheet1 & "'!C1), COUNTA('" & DataSheet1 & "'!R1))"


NOTE:

DataSheet1 = Format(Date - 1, "mm.dd.yy") & " Data"

---

Couldn't you leave you a greenie! Thx, JB, that was exactly what I needed and it worked perfectly.