How to use Transpose Column to Excel Sheets using VBA?
Tags: tousetransposecolumn, vbamacros
Published: 2021-09-13 11:44:03

---
title: How to use Transpose Column to Excel Sheets using VBA?
published: true
date: 2021-09-08 05:38:59 UTC
tags: ToUseTransposeColumn,VBAMacros
canonical_url: https://geekexcel.com/how-to-use-transpose-column-to-excel-sheets-using-vba/
---

In this tutorial, we will guide you on  **how to use the Transpose Column to Excel Sheets using VBA**. Let’s get them below!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Transpose Columns into Sheets

- Firstly, in the Excel worksheet, you need to **create a workbook** that contains **different sheets for each column**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Different-sheets.png)<figcaption>Different sheets</figcaption>

- Then, you have to go to the **Developer Tab**.
- Now, you need to select the **Visual Basic** option under the **Code** section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- After that, you have to  **copy and paste the code**  given below.

```
 Sub TransposeColsToSheets()
'varibles
Dim wb As Workbook
Dim twb As Workbook
Dim lstRw As Integer
Dim lstCl As Integer
Dim cols As Range

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

'creating new file
Set wb = Workbooks.Add

'saving the file. Replace the path with your destination.
wb.SaveAs "C:\Users\Manish Singh\Desktop\Excel Tip\result.xlsx"

Set twb = ThisWorkbook
twb.Sheets(1).Activate
lstCl = Cells(1, Columns.Count).End(xlToLeft).Column

'identfying headers for city names
Set cols = Range(Cells(1, 1), Cells(1, lstCl))

'loop to create sheets
For x = 1 To cols.Count
 wb.Sheets.Add.Name = "page" & x
Next

'loop to transpose columns to sheets
For Each sh In twb.Sheets
 For x = 1 To cols.Count
  sh.Activate
  lstRw = Cells(Rows.Count, 1).End(xlUp).Row
  Range(Cells(1, x), Cells(lstRw, x)).Copy
  wb.Sheets("page" & x).Activate
  lstCl = Cells(1, Columns.Count).End(xlToLeft).Column + 1
  Range(Cells(1, lstCl), Cells(1, lstCl)).PasteSpecial xlPasteAll
 Next x
Next sh

'saving and closing the result workbook
wb.Save
wb.Close

With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-22-954x1024.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to make sure that your  **macro name is selected**  and click the  **Run ** button.

![](https://geekexcel.com/wp-content/uploads/2021/09/Run-the-Code-10.png)<figcaption>Run the Code</figcaption>

- Finally, you will get  **output ** as **transpose column** in given below in the Excel.

![](https://geekexcel.com/wp-content/uploads/2021/09/Transpose.png)<figcaption>Transpose Output</figcaption>

## A Brief Synopsis

Here, we have explained the step-by-step procedure on **how to use the Transpose Column to Excel Sheets using VBA**. Make use of this. Please share your worthwhile  **feedback**  in the below  **comment**  section. To learn more, check out our website **[Geek Excel](https://geekexcel.com/)!!**

**Read Ahead:**

- **[Transpose the Data from Column to Row in Excel Office 365!!](https://geekexcel.com/transpose-the-data-from-column-to-row-in-excel/)**
- **[Transpose Values Based on Multiple Lookup Criteria in Excel Office 365!!](https://geekexcel.com/transpose-values-based-on-multiple-lookup-criteria-in-excel-office-365/)**
- **[How To Use Excel TRANSPOSE Function in Office 365?](https://geekexcel.com/how-to-use-transpose-function-in-microsoft-excel-365/)**
- **[Excel Formulas: Multi-criteria Lookup and Transpose!!](https://geekexcel.com/excel-formulas-multi-criteria-lookup-and-transpose/)**
- **[How to Copy Multiple Sheets Multiple Times in Excel Office 365?](https://geekexcel.com/how-to-copy-multiple-sheets-multiple-times-in-excel-office-365/)**