VBA beginner

BobBridges · #3 09-02-2013, 07:38 PM

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:

Code:

Dim soFm
Sub main()

Set wo = ActiveWorkbook
Set soFm = wo.Worksheets("Sheet1")

CopyRpt wo.Worksheets("Sheet2"), "Membership Download Spain"
CopyRpt wo.Worksheets("Sheet3"), "Pure Premium Download - Spain"

End Sub
' Look for a report name and copy the data found there to the target worksheet.
Sub CopyRpt(so, ReportName)
  Dim coA, coZ

  ' Find the report title.  I'm just filling in the few arguments that I think are likely
  ' to be needed.  Feel free to fill in more according to your own judgement.  By the way,
  ' use xlValues rather than xlFormulas; ask if you aren't sure of the difference.
  Set coA = soFm.Cells.Find(What:=ReportName, LookIn:=xlValues)

  ' Find the upper left and bottom right cells of the report.
  Set coA = coA.End(xlDown) 'to find the next non-blank cell under the title
  Set coZ = coA.End(xlDown).End(xlToRight) 'to find the last row and col

  ' Copy the range to the target sheet.
  so.Cells.ClearContents 'in case there was anything in the target sheet before
  Range(coA, coZ).Copy so.Cells
  End Sub

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).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Beginner VBA	funkyfido	Excel	1	08-27-2013 11:35 AM
Beginner needing help	primmer3001	Office	1	08-22-2011 11:28 AM
Complete beginner needs some help.	Biofodder	Word VBA	1	03-14-2011 01:49 AM
HELP...Beginner	Leeroy4022	Word VBA	2	02-25-2011 02:16 AM
Just a beginner...be nice please!	Tommy Gun	Project	1	02-18-2009 03:06 AM