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:
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
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):
Dim k As Integer
Dim R As Integer
k = 2
R = 0
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
One into multiple columns:
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
- 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].
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
.ManualUpdate = True
For Each pf In .DataFields
.Function = xlSum
.NumberFormat = "#,##0"
.ManualUpdate = False
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)
||Count numerical values only.
||Standard deviation, based on a sample.
||Standard deviation, based on the whole population.
||Variation, based on a sample.
||Variation, based on the whole population.
||No subtotal function specified.
||Count using Distinct Count analysis.
- 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].
- 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].