ActiveSheet in formula


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!
 
Ad

Advertisements

A

Auric__

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?

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top