ActiveSheet in formula

  • Thread starter Thread starter Steve
  • Start date Start date
S

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 said:
Steve said:
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?
Click to expand...

Try replacing

Sheet1

with

" & Workbooks(strName).Sheets(1).Name & "
Click to expand...

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.
 
Back
Top