title: vba excel Find string, move to an offset location, copy dynamic range to another sheet
body: <p>What I have been doing previously is manually selecting and then copy/paste raw data from a report into a sheet titled "ImportDump". From here I use VBA to select and copy the 11 ranges I am interested in across to specific locations in Sheet1 and Sheet2. I would explicitly state the ranges that the data occupy in the ImportDump sheet and copy them across. This worked but is no longer simple to do.</p>
  
  <p>Instead, I plan to search for each table heading in Column A in the ImportDump sheet using the <code>Find</code> method, and then use the result of <code>Find</code>, plus an offset, as the starting position of a dynamic range. So for example, the string "Capital Premier" is found in A30, but the range I need starts in B33. I then need all rows down to the next blank cell in Column B, and all columns across to  the next blank column (data always finishes in Column J). And then repeat for all further 11 heading strings. The headings will all appear in Column A, all the tables will have the same offset from the search string result (3,1), and the same number of columns (9), but not necessarily the same number of rows. </p>
  
  <p>I think I know how to do the search <code>IDump.Range("A1:A200").Find(What:="Capital Premier", LookIn:=xlValues, LookAt:=xlPart)</code>, and I'm pretty sure I can use <code>.End(xldown)</code> to select down to the next blank row, but I'm not sure how to combine all that with an offset to express the starting location of my dynamic range. Could someone please help me to solve this?</p>