ActiveSheet in formula

[Steve]

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!

 

[Harald Staff]

Hi

Try replacing

Sheet1

with

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

HTH. Best wishes Harald

 

[Auric__]

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.

 

[Steve]

Awesome. Thanks guys!