Transposing Rows of Data into Columns onto Consolidated Worksheet

lewjan

New Member
Joined
Dec 4, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
709
Office Version
  1. 365
Platform
  1. Windows
Welcome to Mr.Excel forum

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

VBA Code:
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

New Member
Joined
Dec 4, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Welcome to Mr.Excel forum

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

VBA Code:
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

Well-known Member
Joined
Oct 18, 2007
Messages
709
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,454
Messages
5,596,228
Members
414,047
Latest member
debbos

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