run code on opening workbook and apply code to certain sheets
 

I have vba code that I want to run when I open the workbook and I only want
it to apply to sheet3, sheet5 and sheet6. Would someone know how I need to
write this? Thanks.

run code on opening workbook and apply code to certain sheets
 

Something like

Private Sub Workbook_Open()
Mymacro Worksheets("Sheet3")
Mymacro Worksheets("Sheet5")

Mymacro Worksheets("Sheet6")
End Sub

where your macro would be able to take a worksheet object as a parameter and
process that object.

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
I have vba code that I want to run when I open the workbook and I only
want
it to apply to sheet3, sheet5 and sheet6. Would someone know how I need to
write this? Thanks.

run code on opening workbook and apply code to certain sheets
 

Thank you very much. Do I need to put my code with the code you gave me and
make it all as one? and what does "mymacro" represent? Forgive me I am
still learning. Thanks.

"Bob Phillips" wrote:

Something like

Private Sub Workbook_Open()
Mymacro Worksheets("Sheet3")
Mymacro Worksheets("Sheet5")

Mymacro Worksheets("Sheet6")
End Sub

where your macro would be able to take a worksheet object as a parameter and
process that object.

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
I have vba code that I want to run when I open the workbook and I only
want
it to apply to sheet3, sheet5 and sheet6. Would someone know how I need to
write this? Thanks.

run code on opening workbook and apply code to certain sheets
 

mymacro is the macro for your VBA code.

Your code can be in standard code module, it doesn't have to go with this
code. The important thing is to ensue your code can handle variable
worksheet objects.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you very much. Do I need to put my code with the code you gave me
and
make it all as one? and what does "mymacro" represent? Forgive me I am
still learning. Thanks.

"Bob Phillips" wrote:

Something like

Private Sub Workbook_Open()
Mymacro Worksheets("Sheet3")
Mymacro Worksheets("Sheet5")

Mymacro Worksheets("Sheet6")
End Sub

where your macro would be able to take a worksheet object as a parameter
and
process that object.

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
I have vba code that I want to run when I open the workbook and I only
want
it to apply to sheet3, sheet5 and sheet6. Would someone know how I
need to
write this? Thanks.

run code on opening workbook and apply code to certain sheets
 

Thank you again and even more for helping me on Sunday. My code currently
says: ThisWorkbook.Worksheets("sheet3").Activate so I have the code
currently saved under that one spreadsheet while I was testing it. Is that
wrong? Does it need to go somewhere else? I need to put it "somewhere" so
it knows to run on certain spreadsheets. I ran your code and changed
"myMacro" to "colorformat" which is my "sub" name but the code stopped right
after: Private Sub Workbook_Open(). Is that wrong?

"Bob Phillips" wrote:

mymacro is the macro for your VBA code.

Your code can be in standard code module, it doesn't have to go with this
code. The important thing is to ensue your code can handle variable
worksheet objects.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you very much. Do I need to put my code with the code you gave me
and
make it all as one? and what does "mymacro" represent? Forgive me I am
still learning. Thanks.

"Bob Phillips" wrote:

Something like

Private Sub Workbook_Open()
Mymacro Worksheets("Sheet3")
Mymacro Worksheets("Sheet5")

Mymacro Worksheets("Sheet6")
End Sub

where your macro would be able to take a worksheet object as a parameter
and
process that object.

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
I have vba code that I want to run when I open the workbook and I only
want
it to apply to sheet3, sheet5 and sheet6. Would someone know how I
need to
write this? Thanks.

run code on opening workbook and apply code to certain sheets
 

Your macro should now look something like this

Sub colorformat(sh As Worksheet)
sh.Activate
End Sub

This is code that should be placed in a standard code module (InsertModule)
in the VBE, not within the worksheet code module.

The other code should go in the ThisWorkbook code module as I mentioned. To
be doubly sure you could change it to

Private Sub Workbook_Open()
colorformat ThisWorkbook.Worksheets("Sheet3")
colorformat ThisWorkbook.Worksheets("Sheet5")
colorformat ThisWorkbook.Worksheets("Sheet6")
End Sub

although that shouldn't be absolutely necessary as it will be the active
workbook by virtue of being opened, but it does no harm.

I hope your colorformat macro will do more later, as activating one sheet
after another doesn't have a lot of point :-).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you again and even more for helping me on Sunday. My code currently
says: ThisWorkbook.Worksheets("sheet3").Activate so I have the code
currently saved under that one spreadsheet while I was testing it. Is
that
wrong? Does it need to go somewhere else? I need to put it "somewhere"
so
it knows to run on certain spreadsheets. I ran your code and changed
"myMacro" to "colorformat" which is my "sub" name but the code stopped
right
after: Private Sub Workbook_Open(). Is that wrong?

"Bob Phillips" wrote:

mymacro is the macro for your VBA code.

Your code can be in standard code module, it doesn't have to go with
this
code. The important thing is to ensue your code can handle variable
worksheet objects.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you very much. Do I need to put my code with the code you gave
me
and
make it all as one? and what does "mymacro" represent? Forgive me I
am
still learning. Thanks.

"Bob Phillips" wrote:

Something like

Private Sub Workbook_Open()
Mymacro Worksheets("Sheet3")
Mymacro Worksheets("Sheet5")

Mymacro Worksheets("Sheet6")
End Sub

where your macro would be able to take a worksheet object as a
parameter
and
process that object.

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
I have vba code that I want to run when I open the workbook and I
only
want
it to apply to sheet3, sheet5 and sheet6. Would someone know how I
need to
write this? Thanks.

run code on opening workbook and apply code to certain sheets
 

Thanks for the knowledge. You say you hope my colorformat vba will do more
later, as activating one sheet at a time doesn't have a lot of point. Please
help me to fix that. I am so new at this. I want it to work as efficiently
as possible but do not have the knowledge base by any means. By the way
everything seems to be working great I'm thrilled.
"Bob Phillips" wrote:

Your macro should now look something like this

Sub colorformat(sh As Worksheet)
sh.Activate
End Sub

This is code that should be placed in a standard code module (InsertModule)
in the VBE, not within the worksheet code module.

The other code should go in the ThisWorkbook code module as I mentioned. To
be doubly sure you could change it to

Private Sub Workbook_Open()
colorformat ThisWorkbook.Worksheets("Sheet3")
colorformat ThisWorkbook.Worksheets("Sheet5")
colorformat ThisWorkbook.Worksheets("Sheet6")
End Sub

although that shouldn't be absolutely necessary as it will be the active
workbook by virtue of being opened, but it does no harm.

I hope your colorformat macro will do more later, as activating one sheet
after another doesn't have a lot of point :-).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you again and even more for helping me on Sunday. My code currently
says: ThisWorkbook.Worksheets("sheet3").Activate so I have the code
currently saved under that one spreadsheet while I was testing it. Is
that
wrong? Does it need to go somewhere else? I need to put it "somewhere"
so
it knows to run on certain spreadsheets. I ran your code and changed
"myMacro" to "colorformat" which is my "sub" name but the code stopped
right
after: Private Sub Workbook_Open(). Is that wrong?

"Bob Phillips" wrote:

mymacro is the macro for your VBA code.

Your code can be in standard code module, it doesn't have to go with
this
code. The important thing is to ensue your code can handle variable
worksheet objects.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you very much. Do I need to put my code with the code you gave
me
and
make it all as one? and what does "mymacro" represent? Forgive me I
am
still learning. Thanks.

"Bob Phillips" wrote:

Something like

Private Sub Workbook_Open()
Mymacro Worksheets("Sheet3")
Mymacro Worksheets("Sheet5")

Mymacro Worksheets("Sheet6")
End Sub

where your macro would be able to take a worksheet object as a
parameter
and
process that object.

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
I have vba code that I want to run when I open the workbook and I
only
want
it to apply to sheet3, sheet5 and sheet6. Would someone know how I
need to
write this? Thanks.

run code on opening workbook and apply code to certain sheets
 

Jane,

I'll try to help you but you need to lead.

Why I am saying there is not much point as it stands is that activating
sheet3, then 5 then 6 can be done more simply by just activating sheet6.

So my question to you. What do you want your function to do?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thanks for the knowledge. You say you hope my colorformat vba will do
more
later, as activating one sheet at a time doesn't have a lot of point.
Please
help me to fix that. I am so new at this. I want it to work as
efficiently
as possible but do not have the knowledge base by any means. By the way
everything seems to be working great I'm thrilled.
"Bob Phillips" wrote:

Your macro should now look something like this

Sub colorformat(sh As Worksheet)
sh.Activate
End Sub

This is code that should be placed in a standard code module
(InsertModule)
in the VBE, not within the worksheet code module.

The other code should go in the ThisWorkbook code module as I mentioned.
To
be doubly sure you could change it to

Private Sub Workbook_Open()
colorformat ThisWorkbook.Worksheets("Sheet3")
colorformat ThisWorkbook.Worksheets("Sheet5")
colorformat ThisWorkbook.Worksheets("Sheet6")
End Sub

although that shouldn't be absolutely necessary as it will be the active
workbook by virtue of being opened, but it does no harm.

I hope your colorformat macro will do more later, as activating one
sheet
after another doesn't have a lot of point :-).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you again and even more for helping me on Sunday. My code
currently
says: ThisWorkbook.Worksheets("sheet3").Activate so I have the code
currently saved under that one spreadsheet while I was testing it. Is
that
wrong? Does it need to go somewhere else? I need to put it
"somewhere"
so
it knows to run on certain spreadsheets. I ran your code and changed
"myMacro" to "colorformat" which is my "sub" name but the code stopped
right
after: Private Sub Workbook_Open(). Is that wrong?

"Bob Phillips" wrote:

mymacro is the macro for your VBA code.

Your code can be in standard code module, it doesn't have to go with
this
code. The important thing is to ensue your code can handle variable
worksheet objects.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Thank you very much. Do I need to put my code with the code you
gave
me
and
make it all as one? and what does "mymacro" represent? Forgive
me I
am
still learning. Thanks.

"Bob Phillips" wrote:

Something like

Private Sub Workbook_Open()
Mymacro Worksheets("Sheet3")
Mymacro Worksheets("Sheet5")

Mymacro Worksheets("Sheet6")
End Sub

where your macro would be able to take a worksheet object as a
parameter
and
process that object.

'This is workbook event code.
'To input this code, right click on the Excel icon on the
worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
I have vba code that I want to run when I open the workbook
and I
only
want
it to apply to sheet3, sheet5 and sheet6. Would someone know
how I
need to
write this? Thanks.