Click the “Left Column" option if categories are listed down the left side of the spreadsheet.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.If all of the spreadsheets use the same category headings, you can merge data by category name regardless of where the categories and data are located on the spreadsheets.You can also drag to select the desired cells to merge in the wizard to reference the cells by position only, regardless of category name or data type. Open Excel, and then open the first of the spreadsheets to be merged.Randall Blackburn has worked for several Fortune 1000 companies as a technical writer over the past seven years.He has produced a wide variety of technical documentation, including detailed programming specifications and research papers.
Click the file, and then click “OK.” The file path is added to cell references for the merge.After all references from all spreadsheets are listed in the Consolidate Data wizard, click “OK.” The data from the selected spreadsheets is merged into the master sheet.Be sure to save the master sheet once all data is consolidated. Open("C:\Users\New folder\Master Project list (2).xlsx") Set rng = wb Master. Range("A1: D34") my Path = "C:\Users\New folder\" ' note there is a back slash in the end file = Dir(my Path & "*.xls*") While (file "") Set wb = Workbooks. Paste Exit Sub file = Dir Wend End Sub Sub Macro1() Dim file As String Dim my Path As String Dim wb As Workbook Dim rng As Range Dim wb Master As Workbook 'if master workbook already opened Set wb Master = Workbooks("Master Project list (2).xlsx") 'if master workbook is not opened 'Set wb Master = Workbooks.