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

Опубликовано: 11 Ноябрь 2024
на канале: greggowaffles

10,980

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:
https://gsociology.icaap.org/datauplo...

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