Consolidating data from multiple workbooks

Posted by / 20-Jul-2017 15:19

Consolidating data from multiple workbooks

Calculation = xl Calculation Manual End With 'set path outside the loop Str Prefix = str Folder Name & IIf(b Process Folder, "\", vb Null String) Do While Len(str File Name) 0 'Provide progress status to user Application. Sheets If b New Sheet Then 'All data to a single sheet 'Skip importing target sheet data if the source sheet is blank Set rng2 = ws2. Find("*", ws2.[a1], xl Values, , xl By Rows, xl Previous) If Not rng2 Is Nothing Then Set rng1 = ws1. Find("*", ws1.[a1], xl Values, , xl By Rows, xl Previous) 'Find the first blank row on the target sheet If Not rng1 Is Nothing Then Set rng3 = ws2. Status Bar = vb Null String End With End Sub Function Browse For Folder(Optional Open At As Variant) As Variant 'From Ken Puls as used in his article 'If you manage data in multiple Excel spreadsheets, you can use the Consolidate Data wizard to merge data from several spreadsheets or workbooks into one master sheet.Public Sub Consolidate Sheets() Dim Wb1 As Workbook Dim Wb2 As Workbook Dim ws1 As Worksheet Dim ws2 As Worksheet Dim ws3 As Worksheet Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng Area As Range Dim lrow Space As Long Dim l Sht As Long Dim lng Calc As Long Dim lng Row As Long Dim lng Col As Long Dim X() Dim b Process Folder As Boolean Dim b New Sheet As Boolean Dim Str Prefix Dim str File Name As String Dim str Folder Name As String 'variant declaration needed for the Shell object to use a default directory Dim str Default Folder As Variant b Process Folder = (Msg Box("Process a single folder (Yes)," & vb New Line & "or single file (No)", vb Yes No, "Application Scope: Folder or single File") = vb Yes) b New Sheet = (Msg Box("Extract all data to a single sheet (Yes)," & vb New Line & "or a target file sheet for each source sheet (No)", vb Yes No, "Output Format: Single sheet or sheet by sheet collection") = vb Yes) If Not b Process Folder Then If Not b New Sheet Then Msg Box "There isn't much point creating a exact replica of your source file :)" Exit Sub End If End If 'set default directory here if needed str Default Folder = "C:\temp" 'If the user is collating all the sheets to a single target sheet then the row spacing 'to distinguish between different sheets can be set here lrow Space = 1 If b Process Folder Then str Folder Name = Browse For Folder(str Default Folder) 'Look for xls, xlsx, xlsm files str File Name = Dir(str Folder Name & "\*.xls*") Else str File Name = Application _ . Range("A1: B1") = Array("workbook name", "worksheet count") 'Turn off screenupdating, events, alerts and set calculation to manual With Application . Get Open Filename("Select file to process (*.xls*), *.xls*") End If Set Wb1 = Workbooks. Be sure to save the master sheet once all data is consolidated.Randall Blackburn has worked for several Fortune 1000 companies as a technical writer over the past seven years.

This is why having all spreadsheets open in a single workbook simplifies the process. Column) End If End If Else 'new target sheet for each source sheet ws2. Count) 'Remove any links in our target sheet With Wb1. He has produced a wide variety of technical documentation, including detailed programming specifications and research papers.Randall has also acquired several years' experience writing web content.

consolidating data from multiple workbooks-21consolidating data from multiple workbooks-28consolidating data from multiple workbooks-8

To merge by position and not category, leave the “Top Row” and Left Column” check boxes empty. Click the first cell in the first spreadsheet to be merged, and then drag the cursor over all cells to merge.

One thought on “consolidating data from multiple workbooks”