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.
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.
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.
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.
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...
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.