+ Reply to Thread
Results 1 to 6 of 6

Importing data form other workbooks

  1. #1
    Registered User
    Join Date
    03-02-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Lightbulb Importing data form other workbooks

    Hi Guys

    From all your experience, what is the easiest and quickest way to consolidate many spreadsheets into one master spreadsheet?

    Copying and pasting just takes ages and vlookups are not up for the job due to the volume and number of different spreadsheets. Is there a macro that can just pick password prottected sheets up and paste into a master sheet?

    Basically I have say 10 to 15 spreadsheets that managers have to comment on sales figures on their area then I have a master spreadsheet that I want to consolidate everyone’s results and comments on one page rather than opening each area spreadsheet.

    Many thanks for your help.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Importing data form other workbooks

    This is a very common request. Have a search through the forums and you'll get plenty on answers.

    Alternatively Jerry Beaucaire has loads of examples of things like this on his site. In your case, probably something like https://sites.google.com/a/madrocket...s-to-one-sheet

  3. #3
    Registered User
    Join Date
    03-02-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Importing data form other workbooks

    Thanks Kyle for your advice.

    I have done many searches however just wondering from peoples experience what they thought what the best way was?

    I will check the website out to see if this helps me.

    Thanks :-)

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Importing data form other workbooks

    Jerry's solutions is the general one and most that you see will be variations on this theme. There are other ways that would be more efficient as they don't need to open each workbook, however they require more code and are less flexible

  5. #5
    Registered User
    Join Date
    03-02-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Importing data form other workbooks

    I think this would be much better. If the macro didnt have to open the spreadsheet each time it would be a lot quick due to the size of the spreadsheets. Could you point me in the direction of how to do this? Many thanks.

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Importing data form other workbooks

    Sure, have a look here http://support.microsoft.com/kb/257819, be warned however that unless you are comfortable with macros you would best be sticking to Jerry's solution. The linked approaches the problem with ADO and querying the excel sheet as a database, this is very fast, but can get a bit in depth if your data isn't arranged as a table

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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