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:
gsociology.ica...
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
20 авг 2024