Hi

I am new to VBA and am looking to automate a simple task.

I currently have a workbook which contains two reports on sheet1. The first report is called members. I want to search for the word "members" and then copy the entire range,across and down to sheet 2 on the same workbook. This range will alter every time I run the report. I have a blank row in between the word "members" and the actual data.

I then have another report, a few blank rows after the above report. i want to search for "premium" and then copy the entire range, across and down to sheet 3 on the same workbook. Again I have a blank row after the work premiums. This range will change every time I run the report.



How do I make my code robust so that regardless of how big my report is I can be sure it will always pick up all the data?
Thanks in advance
Liz

I have attached the code which I am currently using but as you can see there is cell references there A5 and A1092 which is making the code not as robust as it should

Attached Files

code.txt (929 Bytes, 12 views)

By "robust" (it's a good word, and I think we both mean the same thing by it) you mean you want your program to copy both reports correctly no matter where they start and how far to the right and down they both go. In another context "robust" might also mean that no user error should get the program upset (like entering a word when it expected a number), but that doesn't arise here so our requirements aren't too complex.

One thing you didn't mention is the shape of the reports, and by that I mean not the number of rows and columns but whether there are any blank rows, columns or cells anywhere inside them. It may be that the reason you didn't mention this issue is that they're both solid rectangles (so to speak) without any blank cells, and I'll start with a solution that depends on that—but if it's not true, we can make the program a little more complex so that it won't be fooled.

In coding, there are always many ways to do the same thing. Here's what I tested:Here are the assumptions my code made:

1) That the active workbook is the one you want—that is, that you're currently looking at the correct workbook.

2) That the source data is in Sheet1 (which your program assumed too).

3) That neither report name appears anywhere else in Sheet1; that's why I omitted LookAt from the Find call.

4) That the sample report names you gave are exactly correct as to case (which enables us to omit the MatchCase argument).

5) That the first column of the report is the column where the report name found - that is, the report name isn't indented or outdented from the report itself.

6) That there are no blank cells in the first row of the report data.

7) That there are no blank cells in the first column of the report data.

8) That the report name need not be copied along with the report itself.

Actually, those are the assumptions that I noticed; no doubt there are others that I assumed, too. Now let the fun begin. Take a look and figure out how it works, and/or ask me "why did you do it that crazy way?" (giving specifics, of course), and/or expostulate "but that won't work, because my reports aren't in the format you assuumed" (again, giving specifics).

Hi Bob

Superb. It all worked a treat. The only issue that i have is that i am missing my header rows in rows 4 and 11. How would I get them in?

Thanks again.

Attached Files

Spreadsheet.xlsm (20.5 KB, 7 views)

Aha. Well, you remember that list of assumptions I wrote down last time? One or two of them leapt up and bit me—or you—and I now notice one that I didn't think of last time, too. (I said there'd be others.) As promised, we can get around this, but let's talk about why it works this way and maybe you'll see how to fix it.

By the way, I'm judging from what you've written so far that you haven't actually looked at the code to see what it does, and from that I infer that you don't know much about VBA programming. But you must; it isn't going to work, in the long run, for you just to accept a bit of code from someone else, plug it in and let it work, because as soon as something changes about your Sheet1, it's going to ding up the results. It's indispensable that you have some idea (it doesn't have to be much) about how this works. So pay attention, students, and apply yourselves, for here it comes:

The first statement, you'll remember, isNow, soFm is the pointer to the "From" worksheet, that is, Sheet1. SoFm.Cells, then, is all the cells in that worksheet. SoFm.Cells.Find, therefore, sets out to find the report name somewhere in those cells. And when it finds a cell with that character string in it, it sets a pointer to it and calls that pointer coA. (In my naming system, "co" is a cell object; typically coA is the first cell and coZ is the last one.)

Ok, so coA points to the cell that contains the report title. But I assumed (correctly, as it turned out) that you don't want to copy the report title with the rest of the report; so the next thing I did was change coA from that cell to the next one down, the next occupied cell below that. Remember, you said you have a blank row between the name of the report and the actual data, so I used the End function to jump to that position.

The End function in VBA/Excel does the same thing that hitting the <End> key does in Excel; if you hit <End>,<Down>, Excel jumps to the last occupied cell of the current group of cells, or the first occupied cell in the next group. So if you've selected A3 and then hit <End>,<Down>, the selection jumps to A5; and that (I assumed) is the start of the report. So I made the next statement this:That changes coA to point to a new cell directly under the old position.

Next I set coZ, by jumping to the bottom and then to the right:Now coA and coZ are the top left and bottom right cells in the report, which I can use to do the copy.

Only one of my assumptions was wrong: The first line of the report is not the first occupied cell directly underneath the name. Well, the first line of the data is, but there are no column headers for A and B. So how do we fix this?

Well, all the methods to do it are easy, but which one you choose depends on how you want to define the report—that is, on what the report may look like six months or six years from now. Consider the following definitions of how to find the first and last cells of the report, starting from the report name:

1) <End>,<Down> twice to get to the bottom row of the report, then <End>,<Right> to get to the rightmost column. That's coZ. CoA is assumed to be in the same column as the report name and one row below it (the column-header row). That works in your sample; but you said there's a blank row between the report name and the report, and if later on there actually is one (or more), this method will start omitting the header row again.

2) <End>,<Down>, then up one row; that's coA. Then <End<,<Down> twice (to get to the last row) and <End>,<Right> once; that's coZ. That'll work for now, and cover the hypothetical future blank line after the report name; but if someday someone decides there should be a blank line between the headers and the data, you'd have to change the program again.

3) <End>,<Down> twice to get to the bottom row of the report, then <End>,<Right> to get to the rightmost column. That's coZ. Then <End>,<Up> to get to the top (header) row of the report; coA is in that row, but the same column as the report name. That works for the first report, but not the second; the blank cells for the policy dates on the Spain row get in the way.

4) <End>,<Down> twice to get to the bottom row of the report, then <Right> several columns—I favor five—to get to the middle of the report. CoZ is <End>,<Right> from there; coZ is on the row <End>,<Up> from there but the same column as the report name. I think this may work the longest, even if people move around the Spain row or the policy-date columns or add blank rows after the report name or column headers. But it depends on there always being at least eight columns in the Premium report.

You see the problem? It's easy to make a method that'll work just now. But you specified "robust", and while I approve, it means you have to predict what other people might do to the report layout to mess up your logic.

There are other, even stronger ways to do it, but each one gets more complex. I'm guessing, if this is your first program, that maybe you'd like to keep it simple—or at least at first, adding complexity as you encounter the need for it.

None of these are solutions expressed in VBA; I'm leaving you to think about this, first, and decide how you want to have the program get around the problem. This calls for thought on your part, and part of the test is to see whether you're willing to do it :-).

I spent a great deal of today working on this.
Yes I understand your code and how it works, I would not implement code that I don't understand as like you say I wouldn't be able to maintain it.

I now have the code working as I want but only without the option explicit being used. When I use this I am having difficulty in defining the user defined fields that you created (and I will probably rename these to something more meaningful to me).
Can you point me in the right direction, either books or websites to show you how to do this?
Thanks again
Liz

I never use Option Explicit. But there's no reason you shouldn't; you can turn it on, and include Dim statements for each variable mentioned in my code that doesn't already have one. Come to think of it, I think that's just wo.

You definitely should change the variable names to something that works for you. I like short names, so I've gotten used to meaningful abbreviations—"meaningful" in the sense that they mean something to me. But the important thing is that your program mean something to you.

But you ask for pointers to how to "do this"; you mean how to change the variable names? That's so easy that I'm doubtful it can be what you meant; you just write over them, just like editing an email. If "soFm" is nonsense to you and you want to use "SourceWSheet" instead, just write "SourceWSheet" wherever the original code uses "soFm". Or use the VBA Editor's Find/Replace function.

But as I said, that's so simple that it can't be what you were asking...?

I managed to solve my own question! I was trying to over complicate things. Thanks for all your help.
Liz

Not using Option Explicit is a disaster waiting to happen. Without it, you can end up with all sorts of strange run-time errors due to mistyped variables. I don't know of a single professional coder who doesn't use it.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]