Transpose Rows to Columns from Multiple Worksheets

I have the same selection of cells in multiple sheets that I would like in one table on a separate (master) sheet. I dont mind if this is within the same workbook. The cell range is a rectangle comprising of N2-S2/N80-S80. I would like the data to be transposed from being in columns to being in rows, pasted one below each other i.e. The master sheet would look like this: A = Headings, B = Sheet1 N, C = Sheet1 O, D = Sheet1 P, E = Sheet1 Q, F = Sheet1 R, G = Sheet1 S, H = Sheet2 N, I = Sheet2 O etc… I hope you can help, this would save me days of work!

22 thoughts on “Transpose Rows to Columns from Multiple Worksheets

  1. Excel Geek

    Hi

    Have you tried the Paste Special Transpose function? How many worksheets do you have that require consolidating into one sheet?

    Thanks

  2. Tim

    I have up to 50 sheets per workbook and approximately 90 workbooks to work through.. So I thought it would be worth some code to save time rather than repeated copy and pastes (transpose). When I first tried to save time I used edit-replace for sheet names in a master sheet, though some of the sheets are named, some are ID numbers (3 digit) so it ended up taking a while. While doing this it seems like I was being very repetitive so thought there must be a faster/more time-friendly way…

  3. Excel Geek

    Ah, yes a bit of code would be of benefit! 🙂

    Put the below code into a module in a new workbook. This workbook will become your master workbook for consolidation. The code will open your workbooks and scroll through the worksheets to pull out of the data in cell range N2 to S80. If there is no data within this range it will skip to the next worksheet.

    When it copies data it will transpose it into another sheet in your master workbook. So for example N2 to N80 will be transposed into row A2 to CA2, S2 to S80 will be transposed to A3 to CA3, and so on down the sheet.

    You will need to create a folder on your desktop and save the master worbook in to it as well. Then within this folder create another folder called data. Put all of you workbooks containg the data into the folder called data.

    Edit this line in the code with the name of the worksheet (inside the “” marks) that will be consolidating all of your data.
    Sheets(“Sheet1”).Select ‘SELECT WORK SHEET THAT WILL CONTAIN DATA – RENAME ACCORDING TO YOUR SHEET!!

    IMPORTANT! – Test this code first before using it on your live data!! And remember to make back ups as well! I am not responsible for any loss.

    Sub Transpose()

    Dim spath As String
    Dim sfile As String
    Dim sht As Worksheet
    Dim control As String
    Dim nextrow As Long

    'ROUTINE TO TRANSPOSE COLUMNS N2 TO S80 INTO ROWS IN A MASTER SHEET

    spath = ThisWorkbook.Path 'FILE PATH TO MASTER WORKBOOK
    control = ThisWorkbook.Name 'DECLARE NAME OF MASTER WORKBOOK

    nextrow = 2 'DICTATES WHERE TO START TRANSPOSING DATA - ROW TWO

    With Application.FileSearch
    .LookIn = spath & "\data\"
    .Filename = ".xls"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute() > 0 Then

    Set newwkbk = ActiveWorkbook

    For i = 1 To .FoundFiles.Count
    Workbooks.Open (.FoundFiles(i))
    sfile = ActiveWorkbook.Name

    For Each sht In ActiveWorkbook.Worksheets

    sht.Select

    If Application.WorksheetFunction.CountA(Range("N2:S80")) > 0 Then 'CHECK TO ENSURE THERE IS DATA IN THE RANGE

    Range(Cells(2, 14), Cells(80, 19)).Select
    Selection.Copy

    'SWITCH TO MASTER SHEET
    Workbooks(control).Activate
    Sheets("Sheet1").Select 'SELECT WORK SHEET THAT WILL CONTAIN DATA - RENAME ACCORDING TO YOUR SHEET!!
    Cells(nextrow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
    nextrow = nextrow + 6

    End If

    Workbooks(sfile).Activate 'SWITCH TO SOURCE DATA WORKBOOK

    Next sht 'PROGRESS TO NEXT SHEET IN WORKBOOK

    Workbooks(sfile).Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Close False

    Next i
    End If

    End With

    End Sub

  4. Tim

    Hi,
    First of all, the idea that this can be sorted is awesome! Can I check a couple of things – as I said I am very new to code. When there are comments in CAPS with a ‘ before – are these just to help me understand what it is doing, or are you wanting me to edit them e.g. spath = ThisWorkbook.Path ‘FILE PATH TO MASTER WORKBOOK – in this situation would I need to put in desktop/Data Transfer/data? Please forgive my lack of understanding! Also when you say ‘SELECT WORK SHEET THAT WILL CONTAIN DATA – RENAME ACCORDING TO YOUR SHEET!! – Is that relating to the spreadsheet filename or the name of the first sheet within the workbook? As I will be doing multiple excel files, would I need to input every name or would the code just find all the documents in the folder called ‘data’? Last thing… After the code is saved do I click ‘run dialogue’ to start it working? Thanks so much for your help!

  5. Excel Geek

    Hi there

    Yes, the comments in uppercase with an ‘ at the beginning are purely for describing what the line of code will do.

    The workbook will automatically workout where it is located on your computer and from that will generate the filepath… this is what
    spath=ThisWorkbook.Path is doing. No need to edit it.

    Sheets("Sheet1").Select 'SELECT WORK SHEET THAT WILL CONTAIN DATA - RENAME ACCORDING TO YOUR SHEET!! This line is to specify the name of the sheet that you want to consolidate all the data on to. This will be in the workbook that also contains this code.

    You do not need to specify the workbook names, nor the worksheet names, for your workbooks that contain the data that you wish to consolidate. So long as all these files are put into the folder called data it will pick them up when running the code. The code counts the number of workbooks in the folder called data and then works it way through each workbook one by one, sheet by sheet.

    The code itself doesn’t actually sort the data for you. It is just copying from your various workbooks and putting them into rows in your master file.

    Once the code is saved in a module in your master file then to run it you would click the small green play button on the Visual Basic toolbar and then select the macro from the list to run it. If you don’t have the Visual Basic toolbar then select View — Toolbars — Visual Basic.

    I will upload the excel file that contains the code for you if that is easier?

    REMEMBER to test this code first before using it on your live data!! And remember to make back ups as well! I am not responsible for any loss.

  6. Tim

    aargh – so close!
    I tried to put the code in myself and ended up with a ‘run-time error 445: Object doesn’t support this action’ error message. I tried downloading off the link on your website but that didn’t work (gave me error 404). Sorry this is taking so much of your time

  7. Tim

    Hi,

    I tried using the download and put just one workbook inside the data folder to test to see if would work and an error message came up. The area that it highlighted in yellow began as follows: “With Application.FileSearch”. What would be potential reasons for this to come up?

  8. Tim

    YES! The macro is looking really good! Each sheet relates to an individual, this individual’s name is in cell A1 on each sheet.

    Is there a way to get the content on cell A1 pasted across aswell to be shown against each row of data? Ideally to go in the first column..?

  9. Tim

    That is truly amazing 🙂
    Your title should be Excel Master! I will try to not harass you in the future, thank you so much for your help

  10. Tim

    Hi,

    I posted an update but it didnt get picked up so I thought I best post through here..

    I finally got round to using the macro/master sheet setup sheet you sent, and it did exactly what it was meant to do, and I thought it would all be fine. However I have some gremlins in my data. Within each workbook the data I am looking to copy and transpose out from different sheets are in different cell ranges! I want to shoot the person who inputted this data in the first place!

    So what we have now is a number of workbooks with sheets containing different cell ranges that I would like to copy and transpose out + content of cell A1 as completed above.

    I have found that all the sheets have a similarity that can be used to identify the cell range… Is it possible to use a similar setup to the one above (i.e. all workbooks in a folder + master workbook)but can it do this?:

    *Find cell containing “Class” (will be in between column B and R).
    *Once there select cells from the one containing “Class” across to the next cell that contains nothing (or this could be just move across 10 columns)
    *Then drag down to row number 300 (should be enough to get all of it!)
    *Copy and transpose as per above set up (including cell A1)to a master document

    Is this possible?? I sincerely hope so, I was gutted when I realised my data didnt fit the method you sorted. I really hope you can help me out again, I apologise for not checking through my data more extensively the first time. I hope you can help.

  11. John

    Hello, I am still confused what exact fields in the macro I change to reference my worksheet? Only the references surrounded by ” ” ? and leave all else alone? Just data path, xls name, and sheet name to store output??

    Thanks

  12. Ashley

    Thank you Tim and Excel Geek! The version for Excel 2010 did just what I was looking for.

    My original data set was on one worksheet. I first Split Every N Rows From Worksheet which saved Into New Worksheets.

    To save that step it would be nice to use the same transpose function here on work worksheet. Instead of what the macro does now, which is open and select a range of data from a new worksheet each time, it would copy a range for every N rows of data from “Sheet1” and transpose the data into “Sheet2” every nextrow+1.

    If I come up with that solution, I’ll share it here. 🙂

  13. Ashley

    Thank you Tim and Excel Geek! The version for Excel 2010 did just what I was looking for.

    My original data set was on one worksheet. I first split every n wows from worksheet 1 into new worksheets.

    To save that step it would be nice to use the same transpose function here on one worksheet. Instead of what the macro does now, which is open and select a range of data from a new worksheet each time, it would copy a range for every N rows of data from “Sheet1” and transpose the data into “Sheet2” every nextrow+1.

    If I come up with that solution, I’ll share it here. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *