Making VBA code from one WB work on another WB
 

I'm working on basically a complex order taking/proposal/scope of work
spreadsheet and am doing pretty well with the coding for tricks I need.
However, I'm starting to generate a rather large file due to all the
background code. In order to keep the file size down, I want to offload a
lot of the commonly-used VBA code onto another spreadsheet (JOBLOG.xls) that
is always kept running, and call the code from there.

The problem I'm having is how to get the subroutines to look back and work
on the spreadsheet that's calling them. The names change dynamically, so
this is part of the problem.

For example, the spreadsheet starts off as NEWORDER.xls. One of the subs
I'd like to have in JOBLOG.xls is subSaveFileAs. This will basically look at
some cells on NEWORDER.xls to name the file in a very specific format,
concatenate the filename from that, then rename NEWORDER.xls to (for this
example) CUSTORDER1.xls.

How do I get subSaveFileAs to look into NEWORDER.xls, then adapt to it when
it re-names it CUSTORDER1.xls, plus also be able to re-run the sub (say, to
create a second version of the file as CUSTORDER1a.xls) and have it look at
whichever worksheet called it?

I assume that there's some simple identifier that I could append to each
range reference (like "CallingWorkbook." to "shtCode.Range("B1")) to make
this work easily, but I don't know what it is and haven't been able to locate
it in my reference books. Apparently I don't speak 'crossreferencese' very
well.

Thanks in advance for any help.

- Forest Ramsey

Making VBA code from one WB work on another WB
 

Hi ForestRamsey,

Try ActiveWorkbook.


---
Regards,
Norman



"ForestRamsey" wrote in message
...
I'm working on basically a complex order taking/proposal/scope of work
spreadsheet and am doing pretty well with the coding for tricks I need.
However, I'm starting to generate a rather large file due to all the
background code. In order to keep the file size down, I want to offload a
lot of the commonly-used VBA code onto another spreadsheet (JOBLOG.xls)
that
is always kept running, and call the code from there.

The problem I'm having is how to get the subroutines to look back and work
on the spreadsheet that's calling them. The names change dynamically, so
this is part of the problem.

For example, the spreadsheet starts off as NEWORDER.xls. One of the subs
I'd like to have in JOBLOG.xls is subSaveFileAs. This will basically look
at
some cells on NEWORDER.xls to name the file in a very specific format,
concatenate the filename from that, then rename NEWORDER.xls to (for this
example) CUSTORDER1.xls.

How do I get subSaveFileAs to look into NEWORDER.xls, then adapt to it
when
it re-names it CUSTORDER1.xls, plus also be able to re-run the sub (say,
to
create a second version of the file as CUSTORDER1a.xls) and have it look
at
whichever worksheet called it?

I assume that there's some simple identifier that I could append to each
range reference (like "CallingWorkbook." to "shtCode.Range("B1")) to make
this work easily, but I don't know what it is and haven't been able to
locate
it in my reference books. Apparently I don't speak 'crossreferencese'
very
well.

Thanks in advance for any help.

- Forest Ramsey

Making VBA code from one WB work on another WB
 

DOH! I knew it would be something simple. Thanks. Much appreciated.

"Norman Jones" wrote:

Hi ForestRamsey,

Try ActiveWorkbook.


---
Regards,
Norman



"ForestRamsey" wrote in message
...
I'm working on basically a complex order taking/proposal/scope of work
spreadsheet and am doing pretty well with the coding for tricks I need.
However, I'm starting to generate a rather large file due to all the
background code. In order to keep the file size down, I want to offload a
lot of the commonly-used VBA code onto another spreadsheet (JOBLOG.xls)
that
is always kept running, and call the code from there.

The problem I'm having is how to get the subroutines to look back and work
on the spreadsheet that's calling them. The names change dynamically, so
this is part of the problem.

For example, the spreadsheet starts off as NEWORDER.xls. One of the subs
I'd like to have in JOBLOG.xls is subSaveFileAs. This will basically look
at
some cells on NEWORDER.xls to name the file in a very specific format,
concatenate the filename from that, then rename NEWORDER.xls to (for this
example) CUSTORDER1.xls.

How do I get subSaveFileAs to look into NEWORDER.xls, then adapt to it
when
it re-names it CUSTORDER1.xls, plus also be able to re-run the sub (say,
to
create a second version of the file as CUSTORDER1a.xls) and have it look
at
whichever worksheet called it?

I assume that there's some simple identifier that I could append to each
range reference (like "CallingWorkbook." to "shtCode.Range("B1")) to make
this work easily, but I don't know what it is and haven't been able to
locate
it in my reference books. Apparently I don't speak 'crossreferencese'
very
well.

Thanks in advance for any help.

- Forest Ramsey