Excel VBA Macro: Transpose Rows to Columns (From One Sheet to Another)
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.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
Пікірлер: 8
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 !! 👏
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?
Thank you very much.
@greggowaffles
2 жыл бұрын
No problem! Thank you for watching
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"
Hi, how about Columns to Rows? Thank you
CAN I MAKE THIS MACRO WITH MACRO RECORDING
@greggowaffles
2 жыл бұрын
im not sure that you can create loops in a macro recording, but i just added the code from this video in the description for you. hope it helps!!