Working "With" multiple worksheets at once

**Jbm444** · 07-01-2010, 03:31 PM

Hi,
I have some lengthy code in certain areas which right now I am just copy/pasting and changing the With Sheet1 to With Sheet2 and so forth. Is there any way to write a "With" statement that affects multiple worksheets at once? Here's my (nonworking) attempt at it (if you replace every instance of "SheetnumeRo" with "Sheet2" or the like, the code functions properly):

Please Login or Register  to view this content.

Ideally, this should just run for sheets 2-6, but obviously that doesn't happen. I also tried "With Sheet1 And Sheet2 And Sheet3...etc" but that doesn't work either. Is there any way to do this besides just copy/pasting this code over and over again for every worksheet?

**davesexcel** · 07-01-2010, 03:39 PM

I suppose a loop through he sheets are what you are looking for

Please Login or Register  to view this content.

**Jbm444** · 07-01-2010, 03:46 PM

davesexcel,
Thanks for taking a look. I realized I didn't make this clear in the original post; I need to do this for just sheets 2-6, but not sheet1. I think your code affects every sheet in the workbook.

**davesexcel** · 07-01-2010, 03:50 PM

The example code I supplied affects all sheets but the sheet Named Total, Change it to the Sheet1 if that is the sheet name you don't want to affect

**Jbm444** · 07-01-2010, 03:55 PM

Aaaand it is "ws" to denote the current sheet before all .sort or .columns etc., sorry for the hasty question that I was able to answer myself. Two last questions, though -- if I wanted to make more than one exception in addition to "Total," how would I do that? And if I wanted to denote the exception as Sheet1 instead of the sheetname, how would that work? Thanks for being so thorough in answering all my questions, I appreciate the help.

**davesexcel** · 07-01-2010, 04:02 PM

ActiveSheet will only work if you actual select the sheets, if want to use activesheet then before you run your code you will require

Please Login or Register  to view this content.

**Jbm444** · 07-01-2010, 04:37 PM

Because I hastily asked a question, and you're incredibly fast at answering my questions, when I edited my post to answer my own question and ask two new questions, you had already posted a reply.
The upshot being: could you please answer the two new questions I asked in my post before this?
Again, apologies for editing right after I posted, instead of trying to answer myself before posting or just posting my two new questions in a non-edit.

**davesexcel** · 07-01-2010, 04:48 PM

You can Just add another If Statement, check out the original code with the bold change

**Jbm444** · 07-01-2010, 05:07 PM

This should be the last thing on this: The bold addition takes care of my first question, but let's say I don't know the name of Sheet1 when I'm running the code. It could be "Sheet1" or it could be "Report" or maybe "7/2/2009 Report." I still want to run the code as is without changing it to account for the different ws.name I'm excepting. How do I tell the ws. code to make an exception not based on the name of the sheet, but rather on the number of the sheet?

**shg** · 07-01-2010, 05:31 PM

Perhaps ...

Please Login or Register  to view this content.

**Jbm444** · 07-01-2010, 05:46 PM

shg,
It runs into an error with the "case" method here:

Please Login or Register  to view this content.

.Rows gets highlighted in an error in this instance.

Also, I was assuming on how the form of case worked: I just had Case 2 To 6 since I only needed sheets 2-6, but if I needed sheets 9 and 14-22 as well I would just use the exact example code you provided. This is correct?

**shg** · 07-01-2010, 05:57 PM

Correct, it's just a list of the relevant sheet indices.

**Jbm444** · 07-01-2010, 06:00 PM

Good to know shg, that should help in the future with using the case method.
However, the error remains, so I wonder if either there's something I should be looking into to defeat it, or stick with davesexcel's original method with some kind of change so that I'm not using sheetnames?

**shg** · 07-01-2010, 06:12 PM

I can't look at one line of code in isolation and know what you're trying to do.

Maybe this:

Please Login or Register  to view this content.

**Jbm444** · 07-01-2010, 06:25 PM

Sorry that wasn't clear, that was just the line of code that errored from the larger code section in the original post.
Your new code seems to work, with the addition of "dim lrow as long" at the top (if anyone else tries to adapt this code). Unfortunately I don't have the time to check for sure today, but it looks like it'll work. Thanks for the help!

Working "With" multiple worksheets at once

LinkBack

Thread Tools

Rate This Thread

Display

Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Re: Working "With" multiple worksheets at once

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions