+ Reply to Thread
Results 1 to 3 of 3

Pulling data from multiple sheets within same workbook

  1. #1
    Registered User
    Join Date
    11-22-2009
    Location
    Newcastle,England
    MS-Off Ver
    Excel 2003
    Posts
    78

    Exclamation Pulling data from multiple sheets within same workbook

    Hi

    I have found the following code that i believe will fetch data from multiple sheets and combine it in to one named "Rollup". This data is fetched from sheets named as per the standard excel format. However my sheets are being imported with the name of the file which they have come from for example:

    FR1-22.11.2009.xls
    RA12-22.11.2009.xls

    The constant that will remain will be the date throughout the sheets names as the number of actual sheets present will vary. How would the code below be edited to reflect this naming convention? assuming the data is to be collated in to "rollup" and how would it be edited to pick out a specific cell or number of cells? at the minute it looks as though it just copies the whole sheet whereas i am looking to copy just specific cells in to "rollup".



    Please Login or Register  to view this content.
    Thanks for any help

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,064

    Re: Pulling data from multiple sheets within same workbook

    That code doesn't pay any attention to what the sheets are called, it just processes all of them.
    What cells would you want copied and to where?
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    11-22-2009
    Location
    Newcastle,England
    MS-Off Ver
    Excel 2003
    Posts
    78

    Re: Pulling data from multiple sheets within same workbook

    Well there are actually loads of different cells i will be using eventually but to be honest if someone could produce a code that referred to 3 or 4 individual cells i could then just edit this with more or less cells to get the information i want.


    Basically what happens is this. Duplicate files are saved to a folder from a workbook that staff use. These duplicates are named in accordance to the team name and the date of the week commencing and held within a folder named after the week commencing date. Each of these files within it has a sheet called "dump" this collates all the data from the different sheets within the workbook (so each duplicate has its own dump file reflecting the info from the rest of its sheets)

    I have a macro which looks within the folder with the date specified in a cell on "main menu" sheet within my analysis workbook. This macro loads all of the files within the specified folder in to new sheets within my analysis workbook. These sheets are named to the file name which they copy the "dump" sheet from. The tab names will therefore look as follows:

    mainmenu
    FR1-23.11.2009.xls
    RA13-23.11.2009.xls
    EU4-23.11.2009.xls

    etc for however many teams save their files for that week (therefore the number of tabs vary and the only constant will be the week commencing date appended to the tab names.)

    The data within each of these sheets remains the same structurally, but obviously the values within each will differ between teams. What I have at present is a sheet called "staff hours" which when the user enters the week commencing date of the folder they wish to retrieve data from it loads up all of the sheets from that folder. I then want to select the cells to do with the staff hours across all sheets and automatically bring them in to the "staff hours" work sheet. The issue is that the number of sheets may vary so i expect some form of loop will be needed to check whether each tab has the week commencing date value as per the cell that the user has typed this in to.

    For ease of example for you guys to produce some code lets just say i want data from the points A5 , C15 and G20 from every work sheet that has been loaded with the only constant in these sheet names being the work commencing date from a cell within "staff days" A1.

    I want these to be paste in the format of column 1 = sheet name data came from , column 2 = a5 value column 3 = c15 column 4 = G20.

    Thanks for helping with this, i know it is a big ask but its literally the only thing now holding me back from completing this project.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1