Use Function to Get Filtered Data From Another Sheet

[Who I Am]

My sub code works fine. But if I turn it into function, it fails. Can I
use a function to get a result from another sheet?

What I am doing is to put the function in sheet1, then the function
will activate sheet2 where it filters a table and get a sum of a column

Thank you

[Tom Ogilvy]

do you mean a function used as a formula in a worksheet like

=MyCustomfuction(Sheet2!A1:Z26,3)

A function can't activate or select. but most of the time you don't need to.

so yes, I would say it can do what you ask, but you will need to clean up
your code.

--
Regards,
Tom Ogilvy


"Who I Am" wrote:

> My sub code works fine. But if I turn it into function, it fails. Can I
> use a function to get a result from another sheet?
>
> What I am doing is to put the function in sheet1, then the function
> will activate sheet2 where it filters a table and get a sum of a column
>
> Thank you
>
>

[Who I Am]

Hi Mr. MVP:

Here is my code. It works. But I have to read the message box and copy
it to my spreadsheet. I want to use function to get the result.

I put it into

Function MonthBillng()
End Function

But it did not work. (I want to put the function in sheet1 and to get
the data from sheet19)

=============================================

Sub MonthBilling()

Dim TotalBilling

Sheet19.Activate
ActiveSheet.Range("A4").Select
Selection.AutoFilter Field:=7, Criteria1:="<>N/A*", Operator:=xlAnd

ActiveSheet.Range("M4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select

TotalBilling = Format(WorksheetFunction.Sum(Selection), "$* #,##0")
Sheet1.Activate

MsgBox TotalBilling

End Sub




Tom Ogilvy wrote:
> do you mean a function used as a formula in a worksheet like
>
> =MyCustomfuction(Sheet2!A1:Z26,3)
>
> A function can't activate or select. but most of the time you don't need to.
>
> so yes, I would say it can do what you ask, but you will need to clean up
> your code.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Who I Am" wrote:
>
> > My sub code works fine. But if I turn it into function, it fails. Can I
> > use a function to get a result from another sheet?
> >
> > What I am doing is to put the function in sheet1, then the function
> > will activate sheet2 where it filters a table and get a sum of a column
> >
> > Thank you
> >
> >