Open workbook, collect data AND Sheet name from every sheet

  • I can wrap my ahead around opening another document, grabbing data from it, and bringing it back, but I've only done that where I've been specific as to which sheet to get the information from. Now I need to open another workbook, and loop through every sheet, collect the data in A1:E30, and bring it back to the spreadsheet from which I'm running the data and add it to a table.


    • I don't quite have a handle on how to set up the loop
    • I also need to grab the sheet name and bring that over.


    The data I'm inheriting is spread across 100 sheets instead of one table, and the sheet name is part of the data - yuck. Any suggestions? Thanks in advance!

  • Re: Open workbook, collect data AND Sheet name from every sheet


    While this is incomplete code, it does show you how to loop through the worksheets in your source workbooks to get the information. I am assuming, hopefully correctly that you will be able to link this into your other codes. If you need additional help, do not hesitate to post back.


    Option Explicit