Running code/switching between sheets
 

I have installed code in the "Master" sheet and I want to
apply some of the code to the "Sjournal" sheet. I have tried the below
code to no avail. Any advice?

Code used

Sheets("Sjournal").Activate


Thanks
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932

Running code/switching between sheets
 

The code in your example activates the sheet "Sjournal" just fine.

However, it sounds like you may actually want code to affect things on the
"Sjournal" sheet. The way I do that kind of thing is create an object
reference to the sheet in question.

Dim objWS As Worksheet

Set objWS = Worksheets("Sjournal")

At this point, if you do something like

objWS.Range("A1").Value = "Test"

you'll be affecting the cell A1 on the "Sjournal" sheet.

Once you've done all you need, you should set the object to nothing to free
up memory.

Set objWS = Nothing


HTH
--
Hmm...they have the Internet on COMPUTERS now!


"Metrazal" wrote:


I have installed code in the "Master" sheet and I want to
apply some of the code to the "Sjournal" sheet. I have tried the below
code to no avail. Any advice?

Code used

Sheets("Sjournal").Activate


Thanks
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932

Running code/switching between sheets
 

Do you mean you have written some VB functions for the Master sheet that you
would like to use for another sheet? You will need to put those routines in
a module under the "Modules" folder and make them public. Right-click on the
Modules folder and insert a new module. Put your Subs and Functions in there.

Public Function MyFunc() As String
....
End Function

"Metrazal" wrote:


I have installed code in the "Master" sheet and I want to
apply some of the code to the "Sjournal" sheet. I have tried the below
code to no avail. Any advice?

Code used

Sheets("Sjournal").Activate


Thanks
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932

Running code/switching between sheets
 

I want the following code to be applied to "Sjournal"

Sub tax()

Sheets("SJournal").Activate
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 Sub


How would I modify the above code to effect "sjournal" when it resides
in "master"?

Thanks,
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932

Running code/switching between sheets
 

Sub tax()

Dim objWS As Worksheet

Set objWS = Worksheets("SJournal")

With objWS

mytext = "ALMFG: AL MFG TAX"
For a = 1 To 99999
If .Cells(a, "C") = mytext Then .Cells(a, "Q") = .Cells(a, "E")
Next a
mytext = "SHMFG: SHELBY CO MFG TAX"
For s = 1 To 99999
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

Set objWS = Nothing

End Sub

My changes
1) created a reference to a worksheet object
2) surrounded your loops in a "With .... End With"
3) Replaced "Cells(x,y)" with ".Cells(x.y)" - note the period. This is used
because of the "With" statement. Without the "With" statement, you'd have to
use "objWS.Cells(x,y)" all the time, and I'm lazy. ;)



--
Hmm...they have the Internet on COMPUTERS now!


"Metrazal" wrote:


I want the following code to be applied to "Sjournal"

Sub tax()

Sheets("SJournal").Activate
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 Sub


How would I modify the above code to effect "sjournal" when it resides
in "master"?

Thanks,
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932

Running code/switching between sheets
 

Is their no other way?

Thanks,

Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932

Running code/switching between sheets
 

What code is needed in the module?
And what code is needed in the sheet to run the module?

Thanks,
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932

Running code/switching between sheets
 

Here is an example. Put your tax subroutine in a module under the Modules
folder as I described above. Include the SheetName arg as shown:

Public Sub tax(SheetName As String)

Sheets(SheetName).Activate

MyText = "ALMFG: AL MFG TAX"
For a = 1 To 9999
If Cells(a, "C") = MyText Then Cells(a, "Q") = Cells(a, "E")
Next a

....etc... (the rest of your code goes here)

End Sub

you can call the tax subroutine from anywhere else in your code using one of
two syntaxes (i.e. with or without the "Call" keyword)

'like this

Call tax("SJournal")

'or

tax "SJournal"

"Metrazal" wrote:


What code is needed in the module?
And what code is needed in the sheet to run the module?

Thanks,
Met


--
Metrazal
------------------------------------------------------------------------
Metrazal's Profile: http://www.excelforum.com/member.php...o&userid=31648
View this thread: http://www.excelforum.com/showthread...hreadid=515932