Click the “Left Column" option if categories are listed down the left side of the spreadsheet.
We then switch back to our main workbook and select the sheet where we want to paste the data, I have assigned this to the variable called “str Where To Copy”. Open str File Name, Update Links:=False, Read Only:=True Set data WB = Active Workbook Range(str Copy Range). We open the data workbook by using the Application. Next we select the data that has been assigned to the copy range and copy to the clipboard. Inside our loop are the 4 variables which are assigned the 1) File name, 2) Copy Range, 3) Where To Copy and 4) Which Column contains the starting cell to paste data. Once we have our first data workbook open, we assign this to the data WB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.The reference is updated with the selected cells in the Reference field of the Consolidate Data dialog box.Note that you can click the “Collapse / Expand” button on the right side of the Reference field to collapse the dialog box so you can easily access the spreadsheet.Randall has also acquired several years' experience writing web content.