Consolidating multiple excel workbooks into one teen dating rights
The sixth column is purposely left blank and represents the separation between the two data sets. So far, the formula in the example looks like because I want an exact match.
On the right side is a list of names and email addresses. kb_id=284 Dim Shell App As Object 'Create a file browser window at the default folder Set Shell App = Create Object("Shell. _ Browse For Folder(0, "Please choose a folder", 0, Open At) 'Set the folder to that selected. 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 'This is more complicated than you need but if you can pick out the relevant bits it will get you started. Open(Str Prefix & str File Name) If Not b New Sheet Then 'add summary details to first sheet ws1. 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 .
Path On Error Go To 0 'Destroy the Shell Application Set Shell App = Nothing 'Check for invalid or non-entries and send to the Invalid error 'handler if found 'Valid selections can begin L: (where L is a letter) or '\\ (as in \\servername\sharename.