run code on opening workbook and apply code to certain sheets

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Guest]

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]

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)