Hi,

I would like to do something advanced for which i don't have any expertise in VBA. Following is what intend to do:

  1. My workbook consists of a reference sheet (the 1st sheet) which acts like a contents page and other sheets which have data in detail. These sheets are named as per what is mentioned on the 1st sheet.
  2. A search box is provided in which the user puts the name of the sheet as specified in the 1st sheet.
  3. Excel locates that sheet and copies data from a specific range and copies it to to a new workbook where the target range has been specified.
  4. Excel then looks up the 1st sheet again and based on the serial number of the originally searched sheet, searches for 2 sheets prior and after, copies the ranges from respective sheets and pastes it at specified target ranges in the new sheet that was created.
  5. For example: sheet1 has a contents table that has a list of all the sheets in that workbook labeled as A,B,C,D,E,F,G. If the user searches for "D", then excel would copy the ranges from "D" as well as from B,C,E,F.

My code currently copies only the searched sheet. Request you to help me:
Please Login or Register  to view this content.