Macro Question

mlipski00

New Member
Joined
Dec 29, 2015
Messages
6
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!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Code:
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

New Member
Joined
Dec 29, 2015
Messages
6
Code:
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

Well-known Member
Joined
May 6, 2011
Messages
2,834
Then make change (data will begin from second row - this can be corrected, but I guess for now this isn't the point):
Code:
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 26).Value =                            Application.Transpose(sheet.Range("C1:C26").Value)
:
 

mlipski00

New Member
Joined
Dec 29, 2015
Messages
6
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

Well-known Member
Joined
May 6, 2011
Messages
2,834
Code:
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 21).Value =                            Application.Transpose(sheet.Range("C6:C26").Value)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,163
Messages
5,629,060
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top