Hello. Grateful for any help with the following task (I'm using Excel 2003). I have 1200+ individual workbooks each containing two worksheets. The workbooks are all identical in format, but contain different data. For background info, each workbook contains details of the composition of individual waste streams. The data was extracted from a database that I do not have access to. Worksheet 1 of each of the 1200+ workbooks is called "DataSheet" and consists of two columns and 187 rows. Column A contains the various headings and column B is the good stuff that I need to copy. The data is a combination of text (some >256 characters) and numbers. I would like to combine the data from all 1200 "DataSheet" worksheets into one single worksheet within a new master workbook. The operation would therefore seem to require a transpose function too. That would leave me with a lovely table, with some 1200+ rows (one for each workbook / waste stream) and 187 columns across for the headings. Ultimatley I need to to the same process for the second worksheet in each of the 1200+ workbooks, but although the data is arranged differently within this worksheet I'm guessing that I could adapt the first macro accordingly. Hope this makes sense (it does to me, but that's not always a good sign!)

First let me say I am not very familiar with Excel. Your problem is interesting. I did do a little research to create a script that will (for a given workBook) transpose your worksheet "Datasheet" such that your Headings ColA are laid out across the top, and your "Goodstuff" ColB is placed in row 2 under the appropriate Heading. This preserves your ColA and ColB, leaves ColC (as a placeholder for your Workbook name), and places your transposed data in Columns D onward. It is only a start but may give you some ideas. Note my Test is only using 12 rows of data, but the script is generic (I think). Heres the script [co----- ' Procedure : GenericTranspose ' Author : jed ' Date : 1/20/2009 ' Purpose : Take a list of entries in column A and B ' starting in Row A1 and ' transpose these into row 1 starting in Column D ' Column B is moved to row 2 starting in column D ' Note Column C is left as a placeholder for the eventual Workbook ID - ' Sub GenericTranspose() Dim rng As Range Dim i As Long Dim j As Long Dim v1 As Long Set rng = Cells(Rows.Count, 1).End(xlUp) v1 = rng.Row For j = 1 To 2 For i = 2 To rng.Row Step v1 Cells(j, "D").Resize(j, v1).Value = _ Application.Transpose(Cells(i, j).Resize(v1 + 2, j)) If j <> 1 Then Cells(j, "C").Value = "WorkbookID" 'this is where Workbook Id would go End If Next i Next j End Sub [/code]