+ Reply to Thread
Results 1 to 5 of 5

Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Sheet

  1. #1
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    Question Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Sheet

    I realise this is likely a mamoth task for an Excel VBA and it should really be a database query, however at present, my only option is multiple linked ranges, or a macro to pull to one range.

    What I need to do, is to copy (not cut, as they need to persist on the original document) any populated rows in range D9:X353 on a sheet named ROI, from all workbooks in a certain folder, and paste values into a data sheet on a master report (empty cells below headers on master start at A2 to U2 and continue as required to capture all data).

    What I envisage the macro doing is as follows:

    - On Data sheet in Master Report, find first populated cell below header (row 1)
    - Select first populated cell to last populated cell in range (A to U) and delete contents
    - Select first empty row in range below header (A2)
    - Open first workbook in Individual Trackers folder
    - Select sheet 'ROI'
    - Select populated rows in range D9:X135
    - Copy
    - Paste values into first empty row in range on Master Report, Data sheet
    - Close Individual Trackers workbook
    - Find next empty row and repeat until all workbooks in Individual Trackers folder has been copied and pasted.

    Is this doable, or am I better off just leaving my data sheet as it is at the moment and adding a new linked range in for each new individual tracker created?

    Many thanks for any help!

    Jenn
    Last edited by Jennasis; 07-10-2013 at 06:12 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Shee

    This should do it:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Shee

    Thanks very much for the response!

    This copies the contents across just great! The only problem is that it also copies cells with blank formula results, plus two rows (containing total formulae) below the tacker we want to copy - it also appears to be copying formats across. this is not the end of the world, as I could write a further macro on the Data sheet to tidy and remove blank lines.

    To make things perhaps easier, the first column in the tracker is manually populated and contains a date - would it be possible to reference column D to see whether this was blank or not before copying and copy to the last populated row with a value in D?

    Many thanks again, this is really quick, too!

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Shee

    WE can apply an AUTOFILTER to the source workbooks. Since we're copying from row 9 down, this means we can use row 8 for the "filter". So, you want to filter everything out that is blank in column D?

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-29-2011
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Shee

    Thank you!

    Works great! I have had to remove the protection from the sheet that it is pulling data from, but this is a minor thing.
    Surprised at how super quick this method is in comparison to links! Will have to use it more often!

    *added rep*

    Jenn x

+ 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