Macro Question

[mlipski00]

Hey I'm new to macros and have a question. I have many workbooks that each have multiple sheets but are consistent in layout. The info I am trying to pull is located in cells C6:C26 of each sheet in each workbook. I want to show that information, transposed, in a separate 'Master' workbook. Any help would be appreciated. thanks!

 

[Sektor]

Sub CombineData()

    Dim book As Workbook, sheet As Worksheet
    Dim thisBook As Workbook, thisSheet As Worksheet
    
    Set thisBook = ActiveWorkbook
    Set thisSheet = thisBook.Sheets(1) 'First sheet of MASTER book
    
    For Each book In Workbooks
        If book.Name <> thisBook.Name Then 'Skip "Master" book
            For Each sheet In book.Sheets
                If TypeOf sheet Is Worksheet Then 'Skip Chart sheets if any
                    With thisSheet
                        .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 26).Value = _
                            Application.Transpose(sheet.Range("C1:C26").Value)
                    End With
                End If
            Next
        End If
    Next

End Sub

 

[mlipski00]

Sub CombineData()

    Dim book As Workbook, sheet As Worksheet
    Dim thisBook As Workbook, thisSheet As Worksheet
    
    Set thisBook = ActiveWorkbook
    Set thisSheet = thisBook.Sheets(1) 'First sheet of MASTER book
    
    For Each book In Workbooks
        If book.Name <> thisBook.Name Then 'Skip "Master" book
            For Each sheet In book.Sheets
                If TypeOf sheet Is Worksheet Then 'Skip Chart sheets if any
                    With thisSheet
                        .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 26).Value = _
                            Application.Transpose(sheet.Range("C1:C26").Value)
                    End With
                End If
            Next
        End If
    Next

End Sub

Thank you for your reply Sektor. That worked except that they are all being transposed in Row 1, I would like the first tab to start in A1 of the master, the second tab to transpose in B1 of the master, etc. Any ideas?

 

[Sektor]

Then make change (data will begin from second row - this can be corrected, but I guess for now this isn't the point):

.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 26).Value =                            Application.Transpose(sheet.Range("C1:C26").Value)

:

 

[mlipski00]

Sorry, I gave you the wrong info. Again, I have multiple work books all with multiple tabs and I want to transpose the data in C6:C26 into a master, formatted so that the first tab is shown in A1:U1 of the master, the next tab is shown in A2:U2 in the master, and so forth. I really appreciate your help.

 

[Sektor]

.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 21).Value =                            Application.Transpose(sheet.Range("C6:C26").Value)