This question came from our site for professional and enthusiast programmers.

1

Based solely on your Worksheets("abc").Activate line it seems you are using multiple sheets in a workbook. To point you in a, hopefully helpful, direction for something like this I would wrap the entire sub in a FOR EACH worksheet IN workbook your code here NEXT worksheet statement. Then within the loop you can set variables that reference whatever sheet it's currently in. EDIT: Also, @ckuhn203 makes a good point, Code Review is very helpful for this sort of question.
–
UserUnknownAug 6 '14 at 22:59

2 Answers
2

I think you've missed the point of having subroutines. The idea is to reduce the problem into small simple steps. Each step of the problem is translated into a subroutine (or a function).

Let's start at 30,000 feet in the air. What you want to do is perform an action on a number of worksheets. I'm assuming that you want to work on every sheet in a given workbook. What we'll do is iterate over the Worksheets collection and change your sub so that it takes in a worksheet as a parameter.

Public Sub Main()
Dim sheet As Worksheet
For Each sheet In Worksheets
SetValues sheet
Next sheet
End Sub

Note that at this point I have not changed any of the code in question. All I've done is define a way to perform an action on each sheet in the active workbook. I did replace the subroutine name slbstd with something a little more meaningful and passed it a worksheet to work on. So, your sub declaration will now look like this.

Private Sub SetValues(sheet As Worksheet)

There are a few small changes to the routine to actually make this work. First, we need to replace any instances of Worksheets("abc") with the parameter sheet. Second, we need to replace "abc" with sheet.Name. The code now looks like this.

At this point, the problem you've asked about is solved, but I'm going to beg you to stay with me. We can make this better yet.

The naming is.... well.... it's bad. Part of the problem is that you've prefixed all of them with the name of the sheet you're working on. I have a feeling that you don't understand variable scope very well. I recommend reading up on it. There was never a need to do this, so let's get rid of all that.

It goes beyond the odd prefixes though. Once we've removed them, we're left with names like xnt, cnt, r, and c. These are cryptic. We have to map the meanings in our minds, and that makes programming that much more difficult to do. Say it in English whenever you can.

sheet.Activate does nothing useful for this subroutine. It can safely be removed.

The method you're using to find the last cell might not be the best one. The UsedRange can produce odd results.

.Cells(1, 1).Address is a really verbose way of saying "$A$1". Just use the string value.

With Sheets("totallist")
values = .Range("$A$1", .Cells(lastRow, lastCol).Address).Value
End With

Okay. I could probably pick at a few more things (like magic numbers and magic strings), but I've already thrown a wall of text at you. Thanks for sticking with me here. I hope you've read past the point where your problem was resolved.

The Sheets collection contains chart sheets if your workbook has sheets of that type; the Worksheets collection only contains, well, worksheets. Hence, you should be using the Worksheets collection when referring to worksheet objects, be it just because it's more precise/specific - it's really just a matter of readability, at the end of the day you're still fetching the exact same object. But being consistent about how you're doing it will help avoid mental question marks when you (or someone else?) look at your code 6 months down the line.

I don't like With blocks used like this:

With Sheets("totallist")
values = .Range("$A$1", .Cells(lastRow, lastCol).Address).Value
End With

Just a few lines below that, you're referring to the same worksheet, outside the With block. You could extend the block to encompass all references to it, but I find that's a lazy way of writing code, and it's also a bit of an abuse of the With keyword.

The idea is to avoid referring to worksheets by their name ("magic strings") as much as possible, so if/when the sheet's name changes, maintenance is less of a paineasier, since there's fewer places to modify.