Transpose Column to Excel Sheets using VBA

Scenario

Assume that you have an excel workbook. It has 3 sheets. A sheet contains 5 columns. Each column has a different city's data. Each sheet contains similar 5 columns with different data of the same cities.

Challenge: 

We need to create a file that contains different sheets for each unique column. Each sheet should contain their respective column's data. In the end, we will have 5 sheets with 3 columns each. In a way, it is transposing columns into sheets.

Logic:

First, we need to create a workbook that contains different sheets for each column. We can do this by looking at the first sheet's headings of our original file.

Next, we need to loop through each sheet of the original sheet to copy each column to the new workbook's corresponding sheets.

VBA Code to Transpose Columns into Sheets.

Let's see the VBA code to transpose the sheets first. I explain it 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 can download the file belove to use it immediately or copy the code to customize it as per your need.

Once you run the code, it will immediately create an excel file that will have 5 sheets, with each sheet containing 3 columns of the same city's data.
Transpose Column To Sheets

How does it work?

In the above example code, we have assumed that the file starts from the first cell of each sheet and each sheet contains the same number of columns.

I the first few lines of code, we have declared the variables that we will need in the procedure. We have saved the columns headers in a variable named cols. We have also created a .xlsx file, named result.xlsx.

Next, we have used a loop to create the same number of sheets in result.xlsx as in header in cols.

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

Next, we have used a nested loop. The first loop is to iterate sheets in the original file. Next loop for copying each column and pasting it into each sheet in results.xlsx file.

In the end, we are saving the workbook result.xlsx and closing it. And it is done.

So yeah guys, this how you can copy each column to a new sheet in excel using VBA. This was asked by our user Mahmood in the comments section. I thought it deserved an article so that it can be available for all of us.  Here you have it. I hope it was explanatory enough. If you still have any doubt or any other query, ask in the comments section below.

Related Articles:

Split Excel Sheet Into Multiple Files Based On Column Using VBA | This VBA code split excel sheet base on unique values in a specified column. Download the working file.

Turn Off Warning Messages Using VBA In Microsoft Excel 2016 | To turn off warning messages that interrupt the running VBA code, we use the Application class.

Add And Save New Workbook Using VBA In Microsoft Excel 2016 | To add and save workbooks using VBA we use Workbooks class. Workbooks.Add adds new workbook easily, however...

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.