Excel Macro question

[MrBond]

I have a macro I'm writing and I'd like to go through every sheet in the workbook and change 3 cells. I have a macro that does this already, but I'd like it to skip the first sheet in the workbook. The macro works, but it puts the three values on this main sheet and overwrites some forumlas that are there (so I can't just add a line to the macro to delete the three things it adds)

How should I do this? I've been googling and playing around with VBA without much success.

 

[FrustratedUser]

Record the Macro manually.

 

[MrBond]

Originally posted by: FrustratedUser
Record the Macro manually.

That won't work - the problem is I'm going to have a variable number of sheets in every spreadsheet, so it needs to do it automatically for EVERY sheet but the "Master" sheet. This is going to be a template that we use for a lot of other workbooks, so it has to be as universal as possible.

 

[FrustratedUser]

* backs out of thread *

 

[TSDible]

This should actually be fairly easy to do.

Does your master sheet always have the same name?

 

[TSDible]

I thought about this, and you have a couple of options.

1. Protect the master sheet. And add an On Error Resume Next statement. Then, when the macro gets and error on the master document, it will just go on to the next one.

2. Add the follwoing lines
Dim i as integer

Then, around your macro, use the following

For i = 2 to Sheets.Count

Code that you want done to each sheet

Next i

This will only work if your "master sheet" is indeed sheet1. Sometimes when documents are edited, these get changed around.

If you need help, let me know.

 

[MrBond]

Yeah, the master sheet will always have the same name - It'll always be sheet1 according to VBA. A lot of the other macro stuff relies on that, so there will be bigger problems than this macro not running if someone renames it to something else 😀.

Thanks for the help, I'll try that out later today

 

[TSDible]

Originally posted by: MrBond
Yeah, the master sheet will always have the same name - It'll always be sheet1 according to VBA. A lot of the other macro stuff relies on that, so there will be bigger problems than this macro not running if someone renames it to something else 😀.

Thanks for the help, I'll try that out later today

If you can protect the master sheet, I think that is your best bet actually... and easy to program too.

If users need to enter values in certain ranges, you can set those in Tools -> Protection -> Allow Users to Edit Ranges

Let me know what you come up with or if you need help.

Feel free to email me a copy of an example workbook and I will make it happen for you.

 

[MrBond]

I figured it out - I was going to go with the protected sheet method and actually got it working great just like that. Then I realized that this macro was filling in headings on cells that didn't need them.

I wrote some if-then statements to check for the existance of average boxes before it input the new headings. To my surprise, that also fixed my problem on the first sheet, since the average boxes don't exist on that sheet.

It runs great now unprotected and will change every other worksheet in the workbook. My boss isn't a big fan of protected sheets, so this should make him happy 😀

Thanks a ton for the help 🙂