Transpose Rows to Columns (From One Sheet to Another) | Excel VBA Macro

Excel VBA Macro: Transpose Rows to Columns (From One Sheet to Another). In this video, we use a nested For Loop to transpose multiple columns on one sheet into rows on another sheet. We create a macro that determines the size of a given range, and then transposed that range to another worksheet. We also go over how to clear the contents from a worksheet with VBA.

Data used in this video:

Code:

Sub transpose_to_another_sheet()

Dim og As Worksheet
Dim ns As Worksheet
Dim count_col As Integer
Dim count_row As Integer

Set og = ThisWorkbook.Sheets(1)
Set ns = ThisWorkbook.Sheets(2)

ns.Cells.ClearContents
og.Activate

count_col = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

For i = 1 To count_col
For j = 1 To count_row

ns.Cells(i, j) = og.Cells(j, i).Text

Next j
Next i

ns.Activate

End Sub

#ExcelVBA #ExcelMacro

[Jojosmith]

Excellent. Thanks millions again Greg for all your hard work and make our lives much easier. Thank you so much for the written code that save me a lot of time to write copy it down from your video. All thumbs up 👍even before your class starts in case I forget. You're genius. BEST CODER !! 👏

[ninabonita]

Hello, do you help with questions via email? I have a daily file that needs to be added into a weekly file, then the weekly into a monthly. Each daily file has 3 tabs (1st shift, 2nd shift and 3rd shift)..its not like a raw file with headers in row 1 its more of a template and I just cannot figure out how to roll this out into VBA. Can you please help me?

[pattydudow]

can the raw data in column a be entered in the transposed data sheet as a string in cell A1? example a1 apples a2 bananas to cell a1 with quotes and commas
"apples", "bananas"

[abbyfranzia]

Hi, how about Columns to Rows?
Thank you

[caturdwiwaluyo]

CAN I MAKE THIS MACRO WITH MACRO RECORDING