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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>

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 (Insert>Module)
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >

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 (Insert>Module)
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
(Insert>Module)
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >