Tag Archives: excel

Split excel worksheets to individual files

If we have about many sheets in excel, and save all the sheets as their own files will definitely was not about to do this by hand. it take a take time!. I found a good tips from the internet, to solve this problem,in which using the macro!.

Here the code:

Sub CreateNewWBS()
 Dim wbThis As Workbook
 Dim wbNew As Workbook
 Dim ws As Worksheet
 Dim strFilename As String
 Set wbThis = ThisWorkbook
 For Each ws In wbThis.Worksheets
 strFilename = wbThis.Path & "/" & ws.Name
 Set wbNew = ActiveWorkbook
 wbNew.SaveAs strFilename
 Next ws
End Sub





Multiple into one columns and vice versa in Excel using macro

If you work with a lot of data in Excel, it is difficult to rearrange multiple column of data into a single column. The traditional way, you cut/copy and paste, and it will take forever (boring and repetitive works). The simple way, you able to used “macro”. Below are codes for macro in Excel for rearrange multiple columns into one and vice versa, respectively.

Multiple into one columns (Ozgrid.com, 2007):

Sub Multiple_Single()
Dim k As Integer
Dim R As Integer

    k = 2
    R = 0

    Columns("A:A").Insert Shift:=xlToRight

    Do Until R > 65536 Or Cells(65536, k).End(xlUp).Value = ""   
        R = R + Range(Cells(1, k), Cells(65536, k).End(xlUp)).Rows.Count
        Range(Cells(1, k), Cells(65536, k).End(xlUp)).Copy Range("A65536").End(xlUp).Offset(1, 0)
        k = k + 1
End Sub

One into multiple columns:

Sub Single_Multiple()
    Dim rng As Range
    Dim iCols As Integer
    Dim lRows As Long
    Dim iCol As Integer
    Dim lRow As Long
    Dim lRowSource As Long
    Dim x As Long
    Dim wks As Worksheet

    Set rng = Application.InputBox _
      (prompt:="Select the range to convert", _
    iCols = InputBox("How many columns do you want?")
    lRowSource = rng.Rows.Count
    lRows = lRowSource / iCols
    If lRows * iCols <> lRowSource Then lRows = lRows + 1

    Set wks = Worksheets.Add
    lRow = 1
    x = 1
    For iCol = 1 To iCols
        Do While x <= lRows And lRow <= lRowSource
            Cells(x, iCol) = rng.Cells(lRow, 1)
            x = x + 1
            lRow = lRow + 1
        x = 1
End Sub


  1. Ozgrid.com, (2007). Move Multiple Columns From Multiple Worksheets Into 1 Column. [online] Available at: http://www.ozgrid.com/forum/showthread.php?t=75449 [Accessed 17 Dec. 2014].

Change the summary calculation of multiple pivot table fields at once using Macro (Excel)

Pivot table is useful to summary calculation for table. However, there isn’t a simple way to adjust multiple fields to the same function. The traditional way, you can right-click on the pivot table and adjust one-by-one.

Fortunately, there is the easy way to change multiple pivot-fields to the same function, by using the macro-excel. The full reference can be obtained from Moxie (2013). Here is a short macro that converts all the fields in a selected pivot table to the Sum function (code as xlSum) as follow:

Figure 1: Macro code for adjust multiple fields to the same function (reproduced from Moxie(2013)) (Note: Make sure you have selected the pivot table to change before playing the macro)
Public Sub PivotFieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie
Dim pf As PivotField
With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
.Function = xlSum
.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub

You can change xlSum with other function such as:  xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar and xlVarP. The detail of each function is illustrated in Table 1.

Table  1: Detail of XlConsolidationFunction enumeration (MSDM Microsoft, 2014)
Member name Description
xlAverage Average.
xlCount Count.
xlCountNums Count numerical values only.
xlMax Maximum.
xlMin Minimum.
xlProduct Multiply.
xlStDev Standard deviation, based on a sample.
xlStDevP Standard deviation, based on the whole population.
xlSum Sum.
xlVar Variation, based on a sample.
xlVarP Variation, based on the whole population.
xlUnknown No subtotal function specified.
xlDistinctCount Count using Distinct Count analysis.


  1. Moxie, D. (2013). How to change multiple pivot table fields to Sum Function – Excel Pivots. [online] Excel Pivots. Available at: http://excelpivots.com/excel/change-multiple-pivot-table-fields-sum-function/ [Accessed 30 Nov. 2014].
  2. MSDM Microsoft (2014). XlConsolidationFunction enumeration (Microsoft.Office.Interop.Excel). [online] Available at: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlconsolidationfunction.aspx [Accessed 30 Nov. 2014].