Transposing Rows of Data into Columns onto Consolidated Worksheet

[lewjan]

First, thank you in advance for taking your time to help me.
I have Excel workbook which can have over 111 worksheets. What i am trying to do is to consolidate some of the information into a single worksheet and have the rows of data transposed into columns.

In each worksheet, rows 1 to 5 would contain data in column A. Rows 6 to 8, the data is in column B.
Is there a way (macros) to consolidate all the data into one single workbook where the data from the individual worksheets would be transposed as columns?

Worksheet1 rows 1 to 8 would be transposed across Consolidated worksheet columns A2-H2
Worksheet2 rows 1 to 8 would be transposed across Consolidated worksheet columns A3-H3
Worksheet3 rows 1 to 8 would be transposed across Consolidated worksheet columns A4-H4
etc., etc.

Thank you again for your help!

Jan

 

[mse330]

Welcome to Mr.Excel forum

Try the below code ... You need to create a sheet named "Consolidated" first

Sub test()

Dim ws As Worksheet, DestWs As Worksheet
Set DestWs = Sheets("Consolidated") '<-- create sheet & change name if desired

For Each ws In Sheets
   If ws.Name <> DestWs.Name Then
      With DestWs.Range("A" & Rows.Count).End(3)(2)
         .Resize(, 5) = ws.[transpose(A1:A5)]
         .Offset(, 5).Resize(, 3) = ws.[transpose(B6:B8)]
      End With
   End If
Next

End Sub

 

[lewjan]

Welcome to Mr.Excel forum

Try the below code ... You need to create a sheet named "Consolidated" first

Sub test()

Dim ws As Worksheet, DestWs As Worksheet
Set DestWs = Sheets("Consolidated") '<-- create sheet & change name if desired

For Each ws In Sheets
   If ws.Name <> DestWs.Name Then
      With DestWs.Range("A" & Rows.Count).End(3)(2)
         .Resize(, 5) = ws.[transpose(A1:A5)]
         .Offset(, 5).Resize(, 3) = ws.[transpose(B6:B8)]
      End With
   End If
Next

End Sub

you are awesome mse330!!!! thank you thank you thank you!!!

 

[mse330]

Glad to help & thanks for the feedback