ActiveSheet in formula
 

Hi All,

I have the following formula in vba:

..Formula = "=COUNTIFS('[" & strName & "]Sheet1'!$E:$E,A2,'[" & strName & "]Sheet1'!$H:$H,""0"")"

The file I am referencing via the variable strName only has one sheet...but what I am finding is that the sheet name is not always Sheet1. How can I modify the formula to select the only sheet in the workbook?

Thanks!

ActiveSheet in formula
 

Hi

Try replacing

Sheet1

with

" & Workbooks(strName).Sheets(1).Name & "

HTH. Best wishes Harald

"Steve" wrote in message
news:3109790.209.1332264505214.JavaMail.geo-discussion-forums@ynbu11...
Hi All,

I have the following formula in vba:

.Formula = "=COUNTIFS('[" & strName & "]Sheet1'!$E:$E,A2,'[" & strName &
"]Sheet1'!$H:$H,""0"")"

The file I am referencing via the variable strName only has one
sheet...but what I am finding is that the sheet name is not always Sheet1.
How can I modify the formula to select the only sheet in the workbook?

Thanks!

ActiveSheet in formula
 

Harald Staff wrote:

"Steve" wrote in message
news:3109790.209.1332264505214.JavaMail.geo-discussion-forums@ynbu11...

I have the following formula in vba:

.Formula = "=COUNTIFS('[" & strName & "]Sheet1'!$E:$E,A2,'[" & strName &
"]Sheet1'!$H:$H,""0"")"

The file I am referencing via the variable strName only has one
sheet...but what I am finding is that the sheet name is not always Sheet1.
How can I modify the formula to select the only sheet in the workbook?

Try replacing

Sheet1

with

" & Workbooks(strName).Sheets(1).Name & "

Alternately, ActiveCell.Parent.Name would also work (or in the specific code
Steve posted, just .Parent.Name). As the Perl slogan says, there's more than
one way to do it.

--
I'll be honest.
I have no freakin' clue what these people are trying to accomplish.

ActiveSheet in formula
 

Awesome. Thanks guys!

On Tuesday, March 20, 2012 11:28:25 AM UTC-6, Steve wrote:
Hi All,

I have the following formula in vba:

.Formula = "=COUNTIFS('[" & strName & "]Sheet1'!$E:$E,A2,'[" & strName & "]Sheet1'!$H:$H,""0"")"

The file I am referencing via the variable strName only has one sheet...but what I am finding is that the sheet name is not always Sheet1. How can I modify the formula to select the only sheet in the workbook?

Thanks!