Copy Columns To Separate Sheets Excel VBA Macro

In this post we see how to copy each column with data to a separate worksheet in Excel using VBA macros. This is useful when dealing with many columns that need to be moved to other sheets to have a separate record for the data in each column or maybe do some further analysis or report. The macro loops through each column in the reference worksheet and creates a new sheet with the name of the column header, while copying the rest of the data to the first column in that new sheet. The macro also sets a different tab color for each new sheet.

Macro/VBA code:

 

  Sub CopyColumnsToSheets()

      Dim refWS As Worksheet, col As Range, wsName As String, ws As Worksheet

 

      Set refWS = ActiveSheet

 

      For Each col In refWS.UsedRange.Columns

          wsName = refWS.Cells(1, col.Column).Value

          Set ws = Sheets.Add(after:=ActiveSheet)

          ws.Name = wsName

          ws.Tab.ColorIndex = 3 + col.Column

          col.Offset(1, 0).Copy ws.Range("A1")

      Next col

 

  End Sub

 

 

Macro explained:

• First we declare a Worksheet object variable to store the reference sheet (probably the active sheet when running the macro), another object variable to store the range while looping through the columns, and a string variable for the name of the name of each worksheet. Then we set the reference sheet variable as the active sheet.
• Now we start a loop through each column within the range with data in the reference sheet. We use the UsedRange property of the Worksheet object to get the range with data in that sheet.
• For each column, we get the value in the first cell (the header) and assign it to the wsName variable. That’s going to be the name of each new worksheet. Then we add a new sheet after the active sheet. Not that each time we add a new worksheet, that becomes the active sheet.
• After adding the worksheet, we change the name (with wsName) and tab color of the sheet. The color is different for each new worksheet as a result of using the column index plus three. That starts in ColorIndex 4, which is green. See more about the color index scale in this page.
• Finally, as part of each loop, we copy the data in the column right after the header. In that way, the new sheet gets the name from the header and contents as the other values in the column. This can easily be changed though, if we want to copy the entire column (including the header). In that case, we can just omit the offset property, or set it to zero for both row and column indexes: Offset(0,0). The data is pasted on column A in the new worksheet, or more specifically, starting from cell A1. That can also be changed as needed. In the same way, the code can be updated to copy only certain columns, or a group of columns in one worksheet, and others in other worksheet.

 

This is how we copy columns to separate sheets in Excel with VBA macros.

Other examples:

• How to Stack Combine Multiple Columns with Excel VBA macros
  
• How to Strike Through Text In Cell with Excel VBA macros
  
• How to Read API JSON Response with Excel VBA macros