This one is evading me. My macro opens another workbook and needs to call a
Public Sub located in a worksheet rather than a module. Is this even
possible? If the macro were in a module, things would work fine but that is
not the case here.

Any help will be greatly appreciated.
--
Best wishes,

Jim

Why can't you put it in a module? you can leave it in the sub, and copy it as
a function in a module
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Jim Jackson" wrote:

> This one is evading me. My macro opens another workbook and needs to call a
> Public Sub located in a worksheet rather than a module. Is this even
> possible? If the macro were in a module, things would work fine but that is
> not the case here.
>
> Any help will be greatly appreciated.
> --
> Best wishes,
>
> Jim

Hi Jim,

This one seems to work for me. Opens a workbook then runs a public Sub named
"Test" that's in the Sheet1 code module.

Dim wb As Workbook

Set wb = Workbooks.Open("D:\Temp\Book1.xls")
Run wb.Name & "!Sheet1.Test"

This way also works:

Dim wb As Workbook

Set wb = Workbooks.Open("D:\Temp\Book1.xls")
wb.Sheets("Sheet1").Test



--
Hope that helps.

Vergel Adriano


"Jim Jackson" wrote:

> This one is evading me. My macro opens another workbook and needs to call a
> Public Sub located in a worksheet rather than a module. Is this even
> possible? If the macro were in a module, things would work fine but that is
> not the case here.
>
> Any help will be greatly appreciated.
> --
> Best wishes,
>
> Jim

Vergel,

Thank you very much. It works perfectly.
I could not make changes to the workbook itself since it is someone else's
property so calling the macro from my own macro was the only choice other
than manually clicking the button on the other one.

Thanks again, both of you for prompt answers and good help.
--
Best wishes,

Jim


"Vergel Adriano" wrote:

> Hi Jim,
>
> This one seems to work for me. Opens a workbook then runs a public Sub named
> "Test" that's in the Sheet1 code module.
>
> Dim wb As Workbook
>
> Set wb = Workbooks.Open("D:\Temp\Book1.xls")
> Run wb.Name & "!Sheet1.Test"
>
> This way also works:
>
> Dim wb As Workbook
>
> Set wb = Workbooks.Open("D:\Temp\Book1.xls")
> wb.Sheets("Sheet1").Test
>
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Jim Jackson" wrote:
>
> > This one is evading me. My macro opens another workbook and needs to call a
> > Public Sub located in a worksheet rather than a module. Is this even
> > possible? If the macro were in a module, things would work fine but that is
> > not the case here.
> >
> > Any help will be greatly appreciated.
> > --
> > Best wishes,
> >
> > Jim