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, is
Code:
Set coA = soFm.Cells.Find(What:=ReportName, LookIn:=xlValues)
Now, 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:
Code:
Set coA = coA.End(xlDown)
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:
Code:
Set coZ = coA.End(xlDown).End(xlToRight)
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 :-).