+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid View

  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
    Forum Expert 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:

    Option Explicit
    
    Sub ROIConsolidation()
    Dim wsMstr As Worksheet, wb As Workbook
    Dim fPATH As String, fNAME As String, NR As Long, LR As Long
    
    Set wsMstr = ThisWorkbook.Sheets("Data")                    'macro stored in master workbook
    fPATH = "C:\Individual Trackers\"                           'edit as needed, remember the final \ in this string
    
    If MsgBox("Clear current data sheet", vbYesNo) = vbYes Then 'option to clear the current master sheet, except headers
        wsMstr.UsedRange.Offset(1).Clear
        NR = 2
    Else                                                        'otherwise we append to old data
        NR = wsMstr.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If
    
    fNAME = Dir(fPATH & "*.xlsx")                               'get the name of the first file in the fPATH
    
    On Error GoTo Next1                                         'if no ROI sheet is found, will skip the copy commands
    Do While Len(fNAME) > 0
        Set wb = Workbooks.Open(fPATH & fNAME)                  'open the found file
    
        With wb.Sheets("ROI")
            LR = .Range("D" & .Rows.Count).End(xlUp).Row        'find the last row of data
            If LR > 8 Then
                .Range("D9:X" & LR).Copy wsMstr.Range("A" & NR) 'copy rows to master sheet
                NR = wsMstr.Range("A" & Rows.Count).End(xlUp).Row + 1       'set NR for next paste job
            End If
        End With
    Next1:
        wb.Close False                                          'close found file
    
        fNAME = Dir                                             'get next filename from fPATH
    Loop                                                        'repeat process
    
    End Sub
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  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
    Forum Expert 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?

    Option Explicit
    
    Sub ROIConsolidation()
    Dim wsMstr As Worksheet, wb As Workbook
    Dim fPATH As String, fNAME As String, NR As Long, LR As Long
    
    Set wsMstr = ThisWorkbook.Sheets("Data")                    'macro stored in master workbook
    fPATH = "C:\Individual Trackers\"                           'edit as needed, remember the final \ in this string
    
    If MsgBox("Clear current data sheet", vbYesNo) = vbYes Then 'option to clear the current master sheet, except headers
        wsMstr.UsedRange.Offset(1).Clear
        NR = 2
    Else                                                        'otherwise we append to old data
        NR = wsMstr.Range("A" & Rows.Count).End(xlUp).Row + 1
    End If
    
    fNAME = Dir(fPATH & "*.xlsx")                               'get the name of the first file in the fPATH
    
    On Error GoTo Next1                                         'if no ROI sheet is found, will skip the copy commands
    Do While Len(fNAME) > 0
        Set wb = Workbooks.Open(fPATH & fNAME)                  'open the found file
    
        With wb.Sheets("ROI")
            .Rows(8).AutoFilter 4, "<>"                         'only show rows that are not blank in column D
            LR = .Range("D" & .Rows.Count).End(xlUp).Row        'find the last row of data
            If LR > 8 Then
                .Range("D9:X" & LR).Copy                        'copy rows to master sheet
                wsMstr.Range("A" & NR).PasteSpecial xlPasteValuesAndNumberFormats       'removes formulas and cell formats
                NR = wsMstr.Range("A" & Rows.Count).End(xlUp).Row + 1       'set NR for next paste job
            End If
        End With
    Next1:
        wb.Close False                                          'close found file
    
        fNAME = Dir                                             'get next filename from fPATH
    Loop                                                        'repeat process
    
    End Sub

  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