Modules and code

Ok,

Very much a newbie to vb and excel macros. I have some coding on one sheet that I would like to have applied to another sheet. I have been told that this should be accomplished by inputing the vb code in a module instead of the sheet. I have two sheets. The one that presently has the code in it is called "master" the one that I want to apply the code to is called "invoice". Finally, to my question: what needs to be listed in the module code section and what needs to be listed in the "master" code section to make this code work on the "invoice" sheet only. I would put the code in the "invoice" sheet but because of some other issues, i can't.

Following is a section of the code I want to implement:

Sub tax()
mytext = "ALMFG: AL MFG TAX"
For a = 1 To 9999
If Cells(a, "c") = mytext Then Cells(a, "q") = Cells(a, "e")
Next a
mytext = "SHMFG: SHELBY CO MFG TAX"
For s = 1 To 9999
If Cells(s, "c") = mytext Then Cells(s, "p") = Cells(s, "e")
Next s
mytext = "PEMFG: PELHAM MFG TAX"
For p = 1 To 9999
If Cells(p, "c") = mytext Then Cells(p, "n") = Cells(p, "e")
Next p
mytext = "HEMFG: HELENA MFG TAX"
For h = 1 To 9999
If Cells(h, "c") = mytext Then Cells(h, "o") = Cells(h, "e")
Next h

Thanks for any help, in advance...

Met

Hi,
put this a general module rather than your "master" sheet: note the
"." in front the Cells which . in cobination with the "With ...." statement,
ensure it is the cells in the "invoice" worksheet which are processed.

Sub tax()
Worksheets("Invoice").Select
With Worksheets("Invoice")
mytext = "ALMFG: AL MFG TAX"
For a = 1 To 9999
If .Cells(a, "c") = mytext Then .Cells(a, "q") = .Cells(a, "e")
Next a
mytext = "SHMFG: SHELBY CO MFG TAX"
For s = 1 To 9999
If .Cells(s, "c") = mytext Then .Cells(s, "p") = .Cells(s, "e")
Next s
mytext = "PEMFG: PELHAM MFG TAX"
For p = 1 To 9999
If .Cells(p, "c") = mytext Then .Cells(p, "n") = .Cells(p, "e")
Next p
mytext = "HEMFG: HELENA MFG TAX"
For h = 1 To 9999
If .Cells(h, "c") = mytext Then .Cells(h, "o") = .Cells(h, "e")
Next h
End With


"Metrazal" wrote:

>
> Ok,
>
> Very much a newbie to vb and excel macros. I have some coding on
> one sheet that I would like to have applied to another sheet. I have
> been told that this should be accomplished by inputing the vb code in a
> module instead of the sheet. I have two sheets. The one that presently
> has the code in it is called "master" the one that I want to apply the
> code to is called "invoice". Finally, to my question: what needs to
> be listed in the module code section and what needs to be listed in the
> "master" code section to make this code work on the "invoice" sheet
> only. I would put the code in the "invoice" sheet but because of some
> other issues, i can't.
>
> Following is a section of the code I want to implement:
>
> Sub tax()
> mytext = "ALMFG: AL MFG TAX"
> For a = 1 To 9999
> If Cells(a, "c") = mytext Then Cells(a, "q") = Cells(a, "e")
> Next a
> mytext = "SHMFG: SHELBY CO MFG TAX"
> For s = 1 To 9999
> If Cells(s, "c") = mytext Then Cells(s, "p") = Cells(s, "e")
> Next s
> mytext = "PEMFG: PELHAM MFG TAX"
> For p = 1 To 9999
> If Cells(p, "c") = mytext Then Cells(p, "n") = Cells(p, "e")
> Next p
> mytext = "HEMFG: HELENA MFG TAX"
> For h = 1 To 9999
> If Cells(h, "c") = mytext Then Cells(h, "o") = Cells(h, "e")
> Next h
>
> Thanks for any help, in advance...
>
> Met
>
>
> --
> Metrazal
> ------------------------------------------------------------------------
> Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
> View this thread: http://www.excelforum.com/showthread...hreadid=516149
>
>

Works Great...
Thanks