Sunday, 30 September 2012

Excel VBA Macro To Create a Folder By Current Date And Time ,Export Active Sheet Data to a New Workbook By Active Sheet Name

Excel VBA Macro To Create a Folder By Current Date And Time ,Export Active Sheet Data to a New Workbook By Active Sheet Name

'This is a Very User Friendly and Power Full Utility Macro which Exports Data From Active Sheet to a New Work Book
'The New Work Book is Created with Active Sheet Name and It has only one Sheet having Active Sheet Name& Data.
'We can use this Macro To Save Dalily Tasks from 'Regular Task Workbook' to a WorkBook with Current Date & Time

Sub CrtTaskShtByToday()
Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim F As String
Dim B As String
Dim S As String
Dim W As Worksheet

On Error Resume Next
Application.DisplayAlerts = False

X = Day(Date) 'Day Value From Date
Y = Month(Date) 'Month Value From Date
Z = Year(Date) 'Year Value From Date
F = X & "-" & Y & "-" & Z

B = Workbooks("MainTask").ActiveSheet.Name 'B=New Book Name
S = Workbooks("MainTask").ActiveSheet.Name 'S=Sheet Name In New Book

MkDir "D:\" & F 'Make a Desired Folder By Current Date & Time

Workbooks(B).Close 'Closes Exported Data Book If It Opens
Workbooks.Add.SaveAs ("D:\" & F & "\" & B & ".xlsx")

'New Book Open By Active Sheet Name To Export Data
'A New Book In a Folder with Current Date& Time will be created

Workbooks(B).Sheets.Add.Name = S
'A Sheet Name same As Book Name Will Be Created

Workbooks("MainTask").Activate
ActiveWorkbook.ActiveSheet.Cells.Copy Workbooks(B).Sheets(S).Range("a1")

'Copies Data From Active Sheet of Active WorkBook to a Newley Created Book With The Same Sheet Name

For Each W In Workbooks(B).Worksheets
If W.Name <> Sheets(S).Name Then
W.Delete
End If ' Deletes Additional Sheets Except Data Sheet in a New Book
Next W

Workbooks(B).Save 'Your New Exported Data Book
Workbooks(B).Close

End Sub

Excel VBA Macro For a Special Task

Excel VBA Macro For a Special Task

Sub MyTasks()

Dim D As String
Dim M As String
Dim Y As String

Dim a As Integer
Dim b As Integer
Dim c As Integer

Dim I As String
Dim J As String
Dim F As String
Dim P As Variant
Dim W As Worksheet

On Error Resume Next
Application.DisplayAlerts = False

I = MsgBox("Do You Wish To Run Macro", vbOKCancel)
If I = vbCancel Then GoTo TPR

D = Day(Date)
M = Month(Date)
Y = Year(Date)

c = 1

F = D & "-" & M & "-" & Y

Workbooks(F).Close
Workbooks(F).Delete
MkDir "C:\Documents and Settings\Administrator\My Documents\" & "TeamTasks"
P = "C:\Documents and Settings\Administrator\My Documents\TeamTasks\"
Workbooks.Add.SaveAs (P & F & ".xlsx")
Workbooks(F).Sheets(1).Name = "TaskSheet"

J = InputBox("Enter Task Book Name ", "My Task Book Name", "Enter Here...")

Workbooks(J).Activate

For Each W In Workbooks(J).Worksheets
W.Activate
For a = 4 To 500

If Cells(a, 11) <> "" Or Cells(a, 12) <> "" Or Cells(a, 13) <> "" Or _
Cells(a, 14) <> "" Or Cells(a, 15) <> "" Or Cells(a, 16) <> "" Or _
Cells(a, 17) <> "" Or Cells(a, 18) <> "" And Cells(a, 1) <> "" Then
c = c + 1

Workbooks(F).Sheets("TaskSheet").Cells(c, 1) = W.Cells(a, 1)
Workbooks(F).Sheets("TaskSheet").Cells(c, 2) = W.Name

For b = 14 To 17
Workbooks(F).Sheets("TaskSheet").Cells(c, 3) = Workbooks(F).Sheets("TaskSheet").Cells(c, 3) + W.Cells(a, b)
Next b

Workbooks(F).Sheets("TaskSheet").Cells(c, 4) = W.Cells(a, 13)
Workbooks(F).Sheets("TaskSheet").Cells(c, 5) = _
Workbooks(F).Sheets("TaskSheet").Cells(c, 3) - Workbooks(F).Sheets("TaskSheet").Cells(c, 4)
Workbooks(F).Sheets("TaskSheet").Cells(c, 6) = Workbooks(F).Sheets("TaskSheet").Cells(c, 5) * 0.175

End If

Next a
c = c + 1
Next W

Workbooks(F).Save
Workbooks(F).Close

TPR:
End Sub

SumProduct Function Example of using Range Names

Sum Product Function Usage using Range Names
SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays. The SUMPRODUCT is a multipurpose function used to find the count and sum based on multiple criteria.
Sumproduct is a Most Powerful Function that can did the Job of the functions like Sum, SumIf, Sumifs, Count, Countif and Countifs.
Syntax:
=SUMPRODUCT (Array1, Array2, Array3, ...)
Example:

Let us suppose if we define the List Names as follows :

SalExeID = A3:A26
Product   = B3:B26
Jan          = C3:C26
Feb          = D3:D26
Mar          = E3:E26
Sales       = C3:E26

Then by using the above List Names in Sumproduct Function , We Can Find The Sum and Count As Follows.

Range/List Names Used in Sumproduct To Find Sum:

Ex1:





Ex2:





Thanks.,
TAMATAM



Excel SumProduct Function To Find Count Based On Multiple Criteria

SumProduct Function To Find Count  Based  On Multiple Criteria

SUMPRODUCT Function :

SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays. The SUMPRODUCT is a multipurpose function used to find the count and sum based on multiple criteria.
Sumproduct is a Most Powerful Function that can did the Job of the functions like Sum, SumIf, Sumifs, Count, Countif and Countifs.
Syntax:
=SUMPRODUCT (Array1, Array2, Array3, ...)
Example:


------------------------------------------------------------------------------------------------------------------------------------------
Declaration Method 1:



------------------------------------------------------------------------------------------------------------------------------------------
Declaration Method 2:



Here :
In Sumproduct Function we have taken three arrays and each array should satisfy a particular criteria, then returns Count.

We can use (*) or (--) symbols in between arrays.In the above arrays there is no Numerical Data Array.



Thanks,
TAMATAM





Excel SUMPRODUCT Function Syntax and Examples

SUMPRODUCT Function:
SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays. The SUMPRODUCT is a multipurpose function used to find the count and sum based on multiple criteria.
Sumproduct is a Most Powerful Function that can did the Job of the functions like Sum, SumIf, Sumifs, Count, Countif and Countifs.

Syntax:
=SUMPRODUCT (Array1, Array2, Array3, ...)

How Many Ways Sumproduct Works:
In how many ways we can use sumprodcuct  is explained in the following example:
Example:


------------------------------------------------------------------------------------------------------------------
Model 1:
=Sumproduct(A3:A11*B3:B11) =200



How Does It Works:


(2*2)+(4*2)+(8*1)+(10*2)+……=200



Note: 

The *(Asterisk) is used in the formula to find the sum of the products of given array values.

------------------------------------------------------------------------------------------------------------------Model 2 :
=Sumproduct(A3:A11+B3:B11) =121



How Does It Works:

(2+2)+(4+2)+(8+1)+(10+2)+……=121

Note: 
The +(Plus) is used in the formula to find the sum of the sums of given array values.
------------------------------------------------------------------------------------------------------------------
Model 3 :
=Sumproduct(A3:A11/B3:B11) =56




How Does It Works:
(2/2)+(4/2)+(8/1)+(10/2)+……=56

Note: 
The /(Division) is used in the formula to find the sum of the remainders of given array values.

NOTE:
If You have any ambiguity in understanding the above Sumproduct Function , Please comment about it.

Thanks.,
TAMATAM








Friday, 28 September 2012

Excel VBA Macro To Count No.of All Rows and Columns In a Active Sheet

Excel VBA Macro To Count No.of All Rows and Columns In a Active Sheet

Sub CountAllRowsCols()
Dim X As Long
Dim Y As Integer


X = ActiveWorkbook.ActiveSheet.Rows.Count
Y = ActiveWorkbook.ActiveSheet.Columns.Count

MsgBox "The Total No.of Rows= " & X & " :: " & "The Total No.of Columns= " & Y

End Sub

Thanks.,
TPR

How To Export Active Sheet Data to a New Workbook By Active Sheet Name

Excel VBA Export Active Sheet Data to a New Workbook By Active Sheet Name
'Macro To Create a Folder By Current Date And Time ,Export Active Sheet Data to a New Workbook By Active Sheet Name

'This is a Very User Friendly and Power Full Utility Macro which Exports Data From Active Sheet to a New Work Book


'The New Work Book is Created with Active Sheet Name and It has only one Sheet having Active Sheet Name& Data.


'We can use this Macro To Save Dalily Tasks from 'Regular Task Workbook' to a WorkBook with Current Date & Time


Sub CrtTaskShtByToday()

Dim X As Integer
Dim Y As Integer
Dim Z As Integer

Dim F As String

Dim B As String
Dim S As String

Dim W As Worksheet


On Error Resume Next

Application.DisplayAlerts = False

X = Day(Date) 'Day Value From Date

Y = Month(Date) 'Month Value From Date
Z = Year(Date) 'Year Value From Date
F = X & "-" & Y & "-" & Z

B = Workbooks("MainTask").ActiveSheet.Name 'B=New Book Name

S = Workbooks("MainTask").ActiveSheet.Name 'S=Sheet Name In New Book

MkDir "D:\" & F 'Make a Desired Folder By Current Date & Time


Workbooks(B).Close 'Closes Exported Data Book If It Opens


Workbooks.Add.SaveAs ("D:\" & F & "\" & B & ".xlsx")


'New Book Open By Active Sheet Name To Export Data

'A New Book In a Folder with Current Date& Time will be created

Workbooks(B).Sheets.Add.Name = S


'A Sheet Name same As Book Name Will Be Created


Workbooks("MainTask").Activate

ActiveWorkbook.ActiveSheet.Cells.Copy Workbooks(B).Sheets(S).Range("a1")

'Copies Data From Active Sheet of Active WorkBook to a Newley Created Book With The Same Sheet Name


For Each W In Workbooks(B).Worksheets

If W.Name <> Sheets(S).Name Then
W.Delete
End If ' Deletes Additional Sheets Except Data Sheet in a New Book
Next W

Workbooks(B).Save 'Your New Exported Data Book

Workbooks(B).Close

End Sub

Wednesday, 26 September 2012

How To Create a Folder and a Excel File By Current Date And Time

Macro To Create a Folder and a Excel File By Current Date And Time
'We can use this Macro To Save Dalily Tasks from 'Regular Task Workbook' to a WorkBook with Current Date & Time

Sub CrtTaskShtByToday()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim e As String


On Error Resume Next
Application.DisplayAlerts = False

A = Day(Date) 'Day Value From Date
B = Month(Date) 'Month Value From Date
C = Year(Date) 'Year Value From Date
E = A & "-" & B & "-" &C

MkDir "D:\MyXlProjects\" & e 'Make a Desired Folder By Current Date & Time

Workbooks(e).Close

Workbooks.Add.SaveAs ("D:\MyXlProjects\" & E & "\" & E & ".xlsx")
'A Folder & File with Current Date& Time will be created

Workbooks("MainTask").Activate
ActiveWorkbook.ActiveSheet.Cells.Copy Workbooks(e).Sheets("Sheet1").Range("a1")
'Copy Data From Active Sheet to a Sheet1 of Newly Created Workbook

Workbooks(e).Save
Workbooks(e).Close

End Sub

Excel VBA Macro To Auto Fill Default , Auto Fill Series

Excel VBA Macro To Auto Fill Default , Auto Fill Series

MODEL 1:
'Macro To AutoFillDefault  

'This Is A User Friendly Macro which Works Based on User Selection

Sub FillDefault()

For X = 1 To 100
Selection.AutoFill Range(ActiveCell.Offset(X, 0), ActiveCell.Offset(0, 0)), xlFillDefault
Next X

End Sub

'Macro To AutoFillSeries

'This Is A User Friendly Macro which Works Based on User Selection

Sub FillSeries()

For X = 1 To 100
Selection.AutoFill Range(ActiveCell.Offset(X, 0), ActiveCell.Offset(0, 0)), xlFillSeries
Next X

End Sub


MODEL 2:
Macro To AutoFillSeries 

Sub AutoFillSeries()

'If You Know Active Cell Then You Can AutoFillSeries As

'Syntax 1:
Cells(2, 1).AutoFill Range("A2:A100"), xlFillSeries

'Syntax 2:
Cells(2, 1).AutoFill Destination:=Range("A2:A100"), Type:=xlFillSeries

End Sub


Macro To AutoFillDefault

Sub AutoFillDefault()

'If You Know Active Cell Then You Can AutoFillDefault As

'Syntax 1:
Cells(2, 1).AutoFill Range("A2:A100"), xlFillDefault

'Syntax 2:
Cells(2, 1).AutoFill Destination:=Range("A2:A100"), Type:=xlFillDefault

End Sub

Tuesday, 25 September 2012

Excel VBA Macro To Concatenate Each Sheet Data In Main Sheet

Excel VBA Macro To Concatenate  Each Sheet Data In Main Sheet
'Each Column of Each Sheet Comes Side By Side(Concatenation Mode) in Main Sheet

Sub ConcateSheets()
Dim X As Integer
Dim Y As Integer
Dim Z As Integer

Dim W As Worksheet
Dim A As Integer

A = 0 'Variable for Column Increment In Main Sheet

For Each W In Worksheets
If W.Name <> Sheets("MAIN").Name Then
W.Activate

For X = 1 To 10

If Cells(X, 1) <> "" Then

Z = Z + 1 'Row Increment Variable

For Y = 1 To 26

Sheets("MAIN").Cells(Z, A + Y) = W.Cells(X, Y)
If Cells(1, Y) = "" Then Exit For

Next Y

End If

Next X

A = A + Y - 1 'Variable for Columns Join In Main Sheet

End If

Z = 0
Next W

End Sub

Excel VBA Macro To Concatenate First Column of Each Sheet In Main Sheet

Excel VBA Macro To Concatenate First Column of Each Sheet In Main Sheet

'First Column of Each Sheet Comes Side By Side(Concatenation Mode) in Main Sheet

Sub ConcateColumns()
Dim X As Integer
Dim Y As Integer
Dim Z As Integer

Dim W As Worksheet
Dim A As Integer

A = 1 'Variable for Column Increment In Main Sheet

For Each W In Worksheets

If W.Name <> Sheets("MAIN").Name Then
W.Activate

For X = 1 To 100

If Cells(X, 1) <> "" Then
Z = Z + 1 'Row Increment Variable
Sheets("MAIN").Cells(Z, A) = W.Cells(X, 1)
End If

Next X

A = A + 1 'Variable for Column Increment In Main Sheet

End If

Z = 0

Next W
End Sub

How To Import First Sheet Data into First Column of Main Sheet , Second Sheet Data In Second Column of Main Sheet and so on......

Excel VBA Macro To Merge First Sheet Data into First Column of  Main Sheet , Second Sheet Data In Second Column of Main Sheet and so on......

Sub ConsolNconcMulCol()
Dim X As Integer
Dim Y As Integer
Dim Z As Integer

Dim W As Worksheet
Dim A As Integer
Dim B As Integer

A = 1 'Variable for Column Increment In Main Sheet

For Each W In Worksheets
If W.Name <> Sheets("MAIN").Name Then
W.Activate

For Y = 1 To 26
For X = 1 To 100

If Cells(X, Y) <> "" Then
Z = Z + 1 'Row Increment Variable
Sheets("MAIN").Cells(Z, A) = W.Cells(X, Y)
End If

Next X
Next Y

A = A + 1 'Variable for Column Increment In Main Sheet
End If

Z = 0

Next W

End Sub

Saturday, 22 September 2012

MicroSoft Excel KeyBoard ShortCuts

BASIC FILE OPERATIONS:
Keyboard Shortcut                             Action Performs

Ctrl + N                                               Open new workbook

Ctrl + O                                               Open existing workbook

Ctrl + W                                              Close active workbook

F12                                                     Save as … different name, location, file
Type

Alt + F4                                               Close Excel

F1                                                       Open help menu


UNDO, RESTORE OR REPEAT ACTIONS:

Keyboard Shortcut                             Action Performs


Ctrl + Z                                                Undo last action

Ctrl + Y                                                Restore undo action

F4                                                       Repeat last action


INSERT, DELETE & COPY CELLS:
Keyboard Shortcut                                        Action Performs


Ctrl + C                                                           Copy selection to clipboard

Ctrl + X                                                           Cut selection to clipboard

Ctrl + V                                                           Paste last selection which cut/copied to clipboard

Delete                                                             Clear contents of current selection
                                                                        (Leave formatting in place)

Ctrl + -                                                            Open Delete Cells dialog box

Ctrl + + (plus)                                                  Open Insert Cells dialog box

Ctrl + ' (apostrophe)                                          Copy contents of cell above into active
Cell; formulas are an exact copy

Ctrl + R                                                           Copy contents of cell to the left into
Active cell; formulas are an exact copy

Shift + F10                                                      Open shortcut - contextual menu;
Use up & down arrow key + enter to make selection


EDIT DATA:
Keyboard Shortcut                                         Action Performs
F2                                                                   Begin editing active cell;
                                                                        Insertion point is at end of cell contents

Home                                                              While in edit mode, move insertion point
to beginning of cell contents

End                                                                 While in edit mode, move insertion point
to end of cell contents

Ctrl + ->                                                          While in edit mode, move insertion point
One word to the right

Ctrl + <-                                                          While in edit mode, move insertion point
One word to the left

Backspace                                                    While in edit mode, delete character to left of insertion point

Delete                                                             While in edit mode, delete character to
Right of insertion point

Ctrl + Delete                                                  While in edit mode, delete all characters
From insertion point to end of cell contents   

F7                                                               Begin Spell Check

Friday, 21 September 2012

Excel VBA Macro To Format Border Styles , Font Styles

Excel VBA Macro To Change Border Styles

Sub BorderStyles()

'Style1:Selection.Borders.LineStyle = xlContinuous
'Style2:Selection.Borders.LineStyle = xlDouble
End Sub

'Macro To Change Font Styles

Sub FontFormats()

With Selection.Font

.Name = "Arial"
.Size = 12
.Italic = True
.ColorIndex = 10
.Bold = True
.Underline = True

End With

End Sub

Thursday, 20 September 2012

Excel VBA Macro To Auto Fill A Specified Number Of Times & Copy The Same

Excel VBA Macro To Auto Fill A Specified Number Of Times & Copy The Same

Sub FillnCopy()
Dim x As Integer
Dim y As Integer
Dim z As Integer


On Error GoTo TPR:
y = InputBox("Enter The Desired No.of Times To Fill:")

For x = 1 To y - 1
z = z + 1
ActiveCell.Offset(z, 0) = ActiveCell.Value
Next x


Range(ActiveCell.Offset(z, 0), ActiveCell.Offset(z - (y - 1), 0)).Copy

TPR:
End Sub

Excel VBA Macro for Data AlignMent

Excel VBA Macro for Data AlignMent

Sub AlignCorrect()
Dim x As Integer
Dim y As Integer


For x = 2 To 500
For y = 1 To 26

Cells(x, y).Rows.RowHeight = 15
Cells(x, y).Columns.ColumnWidth = 15
Cells(x, y).VerticalAlignment = xlCenter
Cells(x, y).HorizontalAlignment = xlCenter


Next y
Next x

End Sub


Some Valid  Alignments:

Horizontal:
xlGeneral
xlLeft
xlRight
xlCenter

Vertical:
xlTop
xlBottom
xlCenter

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts