compiling multple worksheets
Are the sheets in different workbooks or all 15 sheets in the same workbook.
Assume in different workbooks, on the first sheet in the tab order, data starts in cell A1 with column headers (so first requirement is on row 2). Sub GrabSheets() Dim varr As Variant Dim rng As Range Dim i As Long 'expand to include your workbooks. varr = Array("Dept3.xls", "Dept42.xls", "Dept33.xls", _ "Dept36.xls", "Dept99.xls") For i = LBound(varr) To UBound(varr) Set rng = Workbooks(varr(i)).Worksheets(1). _ Range("A1").CurrentRegion Set rng = rng.Offset(rng.Rows.Count, 0). _ Resize(rng.Rows.Count - 1) rng.Copy ThisWorkbook.Worksheets(1). _ Cells(Rows.Count, 1).End(xlUp)(2) Next End Sub You can then sort the data on product code and then do data=Subtotal on product code with sum for quantity to get product sums. Click on the second button in the outline section that will appear in the left side of the sheet and it will collapse to show only the sums by product code. If you want to automate that part, you can turn on the macro recorder while you do it manually. Put the code in a general module in the workbook where the master list will be consolidated on the first sheet. Regards, Tom Ogilvy Ian Truslove wrote in message ... Hi I'm a Supplies Manager in a Hospital. Each day the clinical departments supply me with a excel spreadsheet detailing their requirments for consumables for the next day. I've created these spreadsheets using a "lookup" table which fills in description,specification number and loction within the stores if they type the code number of the product required. I've never used "lookup" before and have been feeling really smug as to my cleverness !!!!! BUT I NOW NEED SOME HELP FOR THE NEXT STAGE. Problem Each of the sheets ( approximately 15 ) may contain the same products, this means I might potentially go to the same location point within the stores multiply times to collect the same product. Question Is it possible to load multiple worksheets ( all in exactly the same format ) into a single worksheet to enable me to sort into stores location and total required for each product? With my limited knowledge I would cut and paste each of the sheets into one master sheet and then sort by location code, I would then total each of the quantitys required by product code. Unfortunately I suspect my super time saving idea would take longer than the old way. I would appreciate any thoughts or suggestions Thanks Ian Truslove |
All times are GMT +1. The time now is 12:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com