Split Data into Multiple Workbook

Hi,

Many times we need to make category wise separate files  from data on a worksheet, here is code that will do it for you.

Here i have a scenario ,Suppose  we have some data in a Range and 2nd Column of Range have some Categories , we want to bifurcate all Record of all Categories in different Excel Files.

Follow the  some simple Steps To Bifurcate the data with in multiple workbook, Original data will remain same.

1) Create a Name of First Cell of Data Range as "rngStart"

2) Create a Name range of First cell on another blank worksheets  as "rngRemoveDuplicate"

3) Copy Paste this Code in VBA Module 

4) Run the Macro

Note : This will save all bifurcated data file at same location where the MainData File is saved. So you can make a separate folder and can save Main Data file in that folder.

Sub SplitAllbyCode()

   

    Dim rngRange    As Range

    Dim wksSheet    As Worksheet

    Dim ArrUniqe

    Dim lngUniqeCount As Long

    Dim wbkNew          As Workbook

   

    Set wksSheet = ThisWorkbook.Worksheets("Sheet1")

    With wksSheet

        Set rngRange = Intersect(Range("rngStart").CurrentRegion, Range("rngStart").CurrentRegion.Offset(2))

        Application.ScreenUpdating = False

        rngRange.Columns(4).Copy Range("rngRemoveDuplicate")

        Range("rngRemoveDuplicate").CurrentRegion.RemoveDuplicates 1

        ArrUniqe = Range("rngRemoveDuplicate").CurrentRegion

        For lngUniqeCount = LBound(ArrUniqe) To UBound(ArrUniqe)

            Range("rngStart").CurrentRegion.Rows(2).AutoFilter 4, ArrUniqe(lngUniqeCount, 1)

            Range("rngStart").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy

            Set wbkNew = Workbooks.Add

            wbkNew.Worksheets(1).Paste

            wbkNew.SaveAs ThisWorkbook.Path & "\" & ArrUniqe(lngUniqeCount, 1)

            wbkNew.Close 1

        Next lngUniqeCount

    End With

        wksSheet.AutoFilterMode = False

        Application.ScreenUpdating = True

        Range("rngRemoveDuplicate").CurrentRegion.ClearContents

       MsgBox lngUniqeCount & "  Files has been splited, Plase find your files at " & vbCrLf & ThisWorkbook.Path

'Free Memory

Set rngRange = Nothing

Set wksSheet = Nothing

Erase ArrUniqe

Set wbkNew = Nothing

End Sub

Thanks for Reading

Gauge Chart : [Dial Chart]

Hello world

Wanna make an Amazing Gauge Chart ?

Its Formally Known as Dial Chart,you can follow some simple steps to create.

  I have used some VBA Code to make the needle animated :

Here is the Code to Move Needle

Sub MoveNiddle()

    DoEvents

    If Range("rngNiddlePos").Value = Range("Sales").Value Then Exit Sub

    Range("rngNiddlePos").Value = Range("rngNiddlePos").Value + 1

    MoveNiddle  ' Recursive Function

End Sub

Sub Move()

    Range("rngNiddlePos").Value = 0

    MoveNiddle

End Sub  

Download Link for animated dial chart is :

http://www.2shared.com/file/VjAbZvKC/DialChart1.html



Download link for Steps - How to Create Dial Chart :
http://www.2shared.com/file/eOFOlhUY/DialChartSteps.html



Hope you will like it

Waterfall chart :

A waterfall chart is an intuitive tool to visualize the cumulative effect of sequentially introduced positive or negative values. 

here is file you can download the waterfall template [Automated through VBA ] , you just need to give data and press button

http://www.2shared.com/file/1kSiTtg7/WaterFall.html

hope you  will enjoy.

Excel Solver :

As I study the solver, I feel this is elder brother of Goal Seek in Excel ,where we can change only one value with Goal seek , Using Solver we can provide many cell for adjusting value as per our scenario , Below is the Parameter required to solver ,

We can add many scenario to see different analysis,

Using Spreadsheets.  Spreadsheets such as Microsoft Excel provide a convenient way to build such a model.  Cells on a worksheet can hold numbers, labels, or formulas that calculate new values.

• The decision variables for a model are simply worksheet cells containing numbers that Solver can change.
• The objective is a cell containing a formula you want Solver to maximize (or minimize) by adjusting the values of the decision variable cells.
• Constraints are logical conditions on formula cells that must be satisfied (specified with <=, = or >= relations). Frontline's various Solver products provide powerful tools for solving, or optimizing, such models.

 Scree shot of Solver :

So, in the example Range A9 is an objective which  has a formula to do some calculation based on arguments provided as reference, Range A1:A8 are the static values which we are need to change by solver, By Pressing Add it will ask for range associated with some logical condition [<=] , after add the Constraints ,Press solve it will adjust the value of All cells to get the final result as you specified in ValueOf parameter.

Thanks for reading ..