Select Range out of Filtered Data

Option Explicit
Dim rowcount As Long
Dim mycoll As Collection, myrng As Range, cell, i As Integer, logintime, logouttime
Sub createAttendance()
On Error Resume Next
Set mycoll = New Collection
Application.ScreenUpdating = False
    rowcount = ThisWorkbook.Sheets(1).Range("C7").End(xlDown).Row
    Set myrng = ThisWorkbook.Sheets(1).Range("C7:C" & rowcount)
    For Each cell In myrng
        mycoll.Add cell, CStr(cell)
    Next
    i = 0
    For i = 1 To mycoll.Count

        ThisWorkbook.Sheets(2).Cells(i + 1, 1) = mycoll(i)
        ThisWorkbook.Sheets(1).Range("C6").AutoFilter field:=3, Criteria1:=mycoll(i)
        Set myrng = ThisWorkbook.Sheets(1).Range("A7:A" & rowcount).SpecialCells(xlCellTypeVisible)
        logouttime = Format(WorksheetFunction.Max(myrng), "hh:mm:ss AMPM")
        logintime = Format(WorksheetFunction.Min(myrng), "hh:mm:ss AMPM")
        ThisWorkbook.Sheets(2).Cells(i + 1, 1) = mycoll(i)
        ThisWorkbook.Sheets(2).Cells(i + 1, 2) = logintime
        ThisWorkbook.Sheets(2).Cells(i + 1, 3) = logouttime
        ThisWorkbook.Sheets(2).Cells(i + 1, 4).Formula = "=" & ThisWorkbook.Sheets(2).Cells(i + 1, 3).Address & "-" & ThisWorkbook.Sheets(2).Cells(i + 1, 2).Address
        ThisWorkbook.Sheets(2).Cells(i + 1, 4).NumberFormat = "hh:mm:ss"
    Next
End Sub

Download File

Create Pivot Chart Using VBA

Option Explicit
Dim pvttbl As PivotTable, mychart As Chart, chrtrng As Range

Sub createPivotchart()
On Error Resume Next

For Each pvttbl In ThisWorkbook.Sheets(1).PivotTables
    ThisWorkbook.Sheets(1).Range(pvttbl.TableRange2.Address).Delete
Next
    ThisWorkbook.Sheets(1).ChartObjects.Delete
    ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets(2).Range("A1").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets(1).Range("B6"), tablename:="Pivottable1"
    Set pvttbl = ThisWorkbook.Sheets(1).PivotTables("pivottable1")
    With pvttbl
            .PivotFields("Region").Orientation = xlRowField
            .PivotFields("Category").Orientation = xlRowField
            .PivotFields("product").Orientation = xlRowField
            .PivotFields("Quantity").Orientation = xlDataField
    End With
    'Set pvttbl = Nothing
    Set chrtrng = pvttbl.TableRange2
    Set mychart = Charts.Add
    Set mychart = mychart.Location(xlLocationAsObject, "Product")
 
        With mychart
                .SetSourceData chrtrng
                .Parent.Top = ThisWorkbook.Sheets(1).Range("B6").Top
                .Parent.Width = ThisWorkbook.Sheets(1).Range("B6:M6").Width
                .Parent.Height = ThisWorkbook.Sheets(1).Range("B7:B24").Height
                .Parent.Left = ThisWorkbook.Sheets(1).Range("B7").Left
                .PlotArea.Format.Fill.Visible = msoTrue
                .PlotArea.Format.Fill.TwoColorGradient msoGradientVertical, 1
                .PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 51, 0)
                .PlotArea.Format.Fill.BackColor.RGB = RGB(72, 80, 255)
                .PlotArea.Format.Fill.GradientStops(1).Position = 0.1
                .PlotArea.Format.Fill.GradientStops(2).Position = 0.9
                .ChartArea.Format.Fill.Visible = msoTrue
                .ChartArea.Format.Fill.TwoColorGradient msoGradientVertical, 1
                .ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 51, 0)
                .ChartArea.Format.Fill.BackColor.RGB = RGB(72, 80, 255)
                .ChartArea.Format.Fill.GradientStops(1).Position = 0.1
                .ChartArea.Format.Fill.GradientStops(2).Position = 0.9
                .HasTitle = True
                .ChartTitle.Caption = "Total Quantity"
                .HasLegend = False
                .SeriesCollection(1).Interior.Color = RGB(135, 220, 55)
        End With
End Sub


Download Chart

Create Hyperlinks for all Files in a Folder

Sub createHyperLink()
Dim I As Integer
Dim fl As File
Dim fldr As Folder
Dim sh As Worksheet
Dim fso As FileSystemObject
Dim path As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    path = .SelectedItems(1)
    Range("A1") = "File Name"
End With

I = 2

Set fso = New FileSystemObject
Set fldr = fso.GetFolder(path)
    For Each fl In fldr.Files
        ThisWorkbook.Sheets(1).Cells(I, 1) = fl.Name
            If ThisWorkbook.Sheets(1).Cells(I, 1) = fl.Name Then
                ThisWorkbook.Sheets(1).Cells(I, 1).Select
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fl.path
            End If
        I = I + 1
    Next fl

End Sub

Move files from One Folder to another through VBA

Option Explicit
Dim sourcepath, destinationpath As String
Dim fso As Object, fl As Object, i As Integer, j As Integer, k As Integer

Sub movefilesfromFolders()
On Error GoTo errorHandler
 
    'return type Application.getopenfile is variant
    sourcepath = Application.GetOpenFilename(MultiSelect:=True)
 
    If IsArray(sourcepath) = True Then
    Set fso = New Scripting.FileSystemObject
        Application.FileDialog(msoFileDialogFolderPicker).Show
        destinationpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
     
            For i = LBound(sourcepath) To UBound(sourcepath)
             
                fso.movefile sourcepath(i), destinationpath & fso.GetFileName(sourcepath(i))
 
            Next
    Else
        MsgBox "File not selected"
    End If
Exit Sub
errorHandler:
    MsgBox "Error" & Err.Description
 

End Sub

Create Comment Chart via VBA

Option Explicit
Dim cmt As Comment, myrng As Range, rowcount As Long, mychart As Chart
Dim pathname As String, i As Long, datarng As Range, axisrng As Range, captionrange As Range

Private Sub Workbook_Open()


On Error Resume Next
Application.ScreenUpdating = False
    i = 2
    rowcount = ThisWorkbook.Sheets(1).Range("A2").End(xlDown).Row
    pathname = "C:\temp\"
    For i = 2 To rowcount
        Set myrng = ThisWorkbook.Sheets(1).Range("G" & i)
        myrng.Comment.Delete

            Set cmt = myrng.AddComment
            Set mychart = Charts.Add
            Set mychart = mychart.Location(xlLocationAsObject, "Sheet1")
            Set datarng = ThisWorkbook.Sheets(1).Range("B" & i & ":F" & i)
            Set axisrng = ThisWorkbook.Sheets(1).Range("B1:F1")
            Set captionrange = ThisWorkbook.Sheets(1).Range("A" & i)
            mychart.SetSourceData Source:=datarng
            mychart.SeriesCollection(1).XValues = axisrng
            mychart.SetElement msoElementChartTitleAboveChart
            mychart.ChartTitle.Caption = captionrange
            mychart.HasLegend = False
         
            mychart.Export pathname & "temp" & i & ".jpg", "jpg"
            With cmt.Shape
                    .Fill.UserPicture ("C:\temp\temp" & i & ".jpg")
                    .ScaleHeight 2, msoFalse
                    .ScaleWidth 3, msoFalse
            End With
        ThisWorkbook.Sheets(1).ChartObjects.Delete
    Next
    Set myrng = Nothing
    Set mychart = Nothing
    Set datarng = Nothing
    Set axisrng = Nothing
    Set captionrange = Nothing



End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyUGRDLV9IbWMxSkk/edit?usp=sharing

Sparkline chart through VBA

Option Explicit
Dim myChart As Chart, rowcount As Long, i As Long, myrng As Range, datarng As Range
Sub createcellChart()
On Error Resume Next
i = 2
myrng.SparklineGroups.Clear
rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
Set myrng = ThisWorkbook.Sheets(1).Range("F2:F" & rowcount)
Set datarng = ThisWorkbook.Sheets(1).Range("A2:E" & rowcount)
myrng.SparklineGroups.Add Type:=xlSparkColumn, SourceData:=datarng.Address
     With myrng.SparklineGroups.Item(1)
        .SeriesColor.Color = RGB(112, 48, 160)
        .LineWeight = 1.5
        With .Points
            .Highpoint.Visible = True
            .Highpoint.Color.Color = RGB(0, 176, 240)
            .Lowpoint.Visible = True
            .Lowpoint.Color.Color = RGB(255, 0, 0)
        End With
    End With

End Sub


https://drive.google.com/file/d/0B23eJ2xd9ODycUJydHM5MEllQTQ/edit?usp=sharing

Read & write *.txt file in VBA

Option Explicit
Dim pathname As String, fso As Object, tso As Object, tempfile As String
Dim linecounter As Integer, tempvar, i As Integer
Sub readtextFile()
    With Application.FileDialog(msoFileDialogFolderPicker)
                .Show
                .Title = "Select .txt File to read"
    pathname = .SelectedItems(1)
   
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    tempfile = pathname & "\data.txt"
Set tso = fso.getFile(tempfile).OpenAsTextStream(1, -2)
linecounter = 0
    While Not tso.AtEndofStream() <> False
        linecounter = linecounter + 1
        i = 0
        tempvar = tso.readline
            For Each cell In Split(tempvar, " ")
                i = i + 1
                    Cells(linecounter, 1) = cell
            Next
    Wend
    Set tso = Nothing
    Set fso = Nothing
End Sub



Dim pathname As String, fso As Object, tso As Object, tempfile As String
Dim linecounter As Integer, tempvar, i As Integer
Sub readtextFile()
    With Application.FileDialog(msoFileDialogFolderPicker)
                .Show
                .Title = "Select .txt File to read"
    pathname = .SelectedItems(1)
   
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    tempfile = pathname & "\data.txt"
Set tso = fso.getFile(tempfile).OpenAsTextStream(1, -2)
linecounter = 0
    While tso.AtEndofStream() <> True
        linecounter = linecounter + 1
        i = 0
        tempvar = tso.readline
            For Each cell In Split(tempvar, " ")
                i = i + 1
                    Cells(linecounter, 1) = cell
            Next
    Wend
    Set tso = Nothing
    Set fso = Nothing
End Sub

Generate Chart on Worksheet_Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$2" Then
        Select Case Target.Value
            Case "SOUTH"
                Call createGraph(Target.Value)
     
            Case "WEST"
                Call createGraph(Target.Value)
            Case "NORTH"
                Call createGraph(Target.Value)
            Case "MIDWEST"
                Call createGraph(Target.Value)
         
        End Select
    End If
End Sub


Dim myChart As Chart, rowcount As Long, datarng As Range, axisrng As Range
Public Sub createGraph(mycriteria As String)
'On Error Resume Next
Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Dashboard").Range(ThisWorkbook.Sheets("Dashboard").Range("D6").CurrentRegion.Address).Clear
    ThisWorkbook.Sheets(2).Range("A1").AutoFilter Field:=1, Criteria1:=mycriteria
    ThisWorkbook.Sheets(2).UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=ThisWorkbook.Sheets(1).Range("D6")
    ThisWorkbook.Sheets(1).ChartObjects.Delete
    rowcount = ThisWorkbook.Sheets(1).Range("D6").End(xlDown).Row
    Set datarng = ThisWorkbook.Sheets(1).Range("J7:J" & rowcount)
    Set axisrng = ThisWorkbook.Sheets(1).Range("D7:D" & rowcount)

    Set myChart = Charts.Add
    Set myChart = myChart.Location(xlLocationAsObject, "Dashboard")
 
        With myChart
            .Parent.Top = ThisWorkbook.Sheets(1).Range("D6").Top
            .Parent.Left = ThisWorkbook.Sheets(1).Range("D10").Left
            .Parent.Width = ThisWorkbook.Sheets(1).Range("D10:J10").Width
            .Parent.Height = ThisWorkbook.Sheets(1).Range("D6:D20").Height
            .SetSourceData Source:=datarng
            .SeriesCollection(1).XValues = axisrng
            .HasTitle = True
            .ChartType = xlColumnClustered
            .SetElement (msoElementChartTitleAboveChart)
            .ChartTitle.Caption = "Sales amount for " & mycriteria
        End With
    End Sub


https://drive.google.com/file/d/0B23eJ2xd9ODyZ2V1LUdYNXVmMFE/edit?usp=sharing

Change Pivottable Datafield Function

Option Explicit
Dim pvttable As PivotTable
Sub createPivottable()
On Error Resume Next
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete
Next
    ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets(3).Range("a2").CurrentRegion.Address).createPivottable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
            .PivotFields("Product Name").Orientation = xlRowField
            .PivotFields("HelpColumn").Orientation = xlColumnField
            .PivotFields("Base Amount").Orientation = xlDataField
           ThisWorkbook.Sheets("Soum").Range("D8").PivotField.Function = xlSum
    End With
 
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODycnptQzRMVElXVEk/edit?usp=sharing

Copy Range in a Faster Way

Option Explicit
Dim rawdata, myrng As Range
Sub copyRange()
rawdata = ThisWorkbook.Sheets(1).Range("a1:b1726")
ThisWorkbook.Sheets(2).Range("a1").Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
End Sub

Split File and send e-mail with Attachment

Task : split an excel file into separate files using particular customer
codes and then email these directly to a particular email account depending
on the  customer code(column A).



Option Explicit
Dim mycoll As Collection, rowcount As Long, myrng As Range, cell As Object, j As Long
Dim destinationwb As Workbook, outApp As Outlook.Application, outMail, newwb As Workbook

Sub createFiles()
Set mycoll = New Collection
On Error Resume Next
rowcount = ThisWorkbook.Sheets(1).Range("A3").End(xlDown).Row

Set myrng = ThisWorkbook.Sheets(1).Range("A3:A" & rowcount)
    For Each cell In myrng
        mycoll.Add cell, CStr(cell)


    Next
    For j = 1 To mycoll.Count
    Set outApp = New Outlook.Application
    Set outMail = outApp.CreateItem(olMailItem)
    Set destinationwb = Workbooks.Add
    destinationwb.SaveAs Filename:=ThisWorkbook.Path & "\" & mycoll(j), FileFormat:=56
        ThisWorkbook.Sheets(1).Range("A2").AutoFilter field:=1, Criteria1:=mycoll(j)
        ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=destinationwb.Sheets(1).Range("A1")
         Set newwb = ActiveWorkbook
     
     
        destinationwb.Save
        With outMail
            .To = mycoll(j) & "@yahoo.com"
            .Subject = "Monthly Account Summary"
         
            .Body = "Hi," & vbNewLine & "     Please find the attachment" & vbNewLine & "Regards," & vbNewLine & "Soumyendu"
         
            .Attachments.Add (ThisWorkbook.Path & "\" & newwb.Name)
            .Display
         
        End With
        Application.DisplayAlerts = False
        destinationwb.Close

         Application.DisplayAlerts = False
         Application.Wait (Now + TimeValue("0:00:02"))
         Application.SendKeys "%s" '
        Set outMail = Nothing
     
        Set outApp = Nothing
     
    Next
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyb2VmRm1vblJjWGM/edit?usp=sharing

Insert Data Through VBA

Option Explicit
Dim conn As New ADODB.Connection

Dim querystring As String
Dim id As Integer, empname As String, age As Integer, address As String, salary As Long, i As Integer, rowcount As Long
Sub insertData()
i = 2
On Error Resume Next
conn.ConnectionString = "Provider=SQL Native Client ;Integrated Security=SSPI; Data Source=New\SQLExpress;Initial Catalog=Somu"
rowcount = ThisWorkbook.Sheets(1).Range("a2").End(xlDown).Row

conn.Openq

    For i = 2 To (rowcount - 1)

            id = CInt(ThisWorkbook.Sheets(1).Range("A" & (i + 1)))
            empname = ThisqWorkbook.Sheets(1).Range("B" & (i + 1))
            age = CInt(ThisWorkbook.Sheets(1).Range("C" & (i + 1)))
            address = ThisWorkbook.Sheets(1).Range("D" & (i + 1))
            salary = CLng(ThisWorkbook.Sheets(1).Range("E" & (i + 1)))
            querystring = "Insert into Customers(Id, EmpName,Age,Address,Salary) values(" & id & ",'" & empname & "' ," & age & ",' " & address & "'," & salary & ")"
         
            conn.Execute querystring
 
    Next
conn.Close
Set conn = Nothing

End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyRUE5Q1luaGdnTUk/edit?usp=sharing

Sort IP Address

Option Explicit
Dim rowcount As Long, myrng As Range, cell As Object, cell1 As Object



Sub sortIPAddress()
rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
Set myrng = ThisWorkbook.Sheets(1).Range("A1:A" & rowcount)
    For Each cell In myrng
      cell.Offset(0, 1) = WorksheetFunction.Substitute(cell, ".", "")
      cell.NumberFormat = "#"
   
    Next
 
    ThisWorkbook.Sheets(1).Range("B1:B" & rowcount).Sort key1:=Range("B1"), Header:=xlYes
    For Each cell1 In ThisWorkbook.Sheets(1).Range("B2:B" & rowcount)
            cell1 = Mid(cell1, 1, 3) & "." & Mid(cell1, 4, 3) & "." & Mid(cell1, 7, 1) & "." & Mid(cell1, 8, Len(cell1) - 7)
         
    Next
    ThisWorkbook.Sheets(1).Range("B1:B" & rowcount).Columns.AutoFit
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODycC1keV8wZHVUREU/edit?usp=sharing

Employee Status report in Userform with Autofilter in VBA

https://drive.google.com/file/d/0B23eJ2xd9ODya2JEUUxTVC01aGM/edit?usp=sharing

Lock/Unlock Range with Criteria

Sub showParameter()
 
    If ThisWorkbook.Sheets(2).Range("J1") = 1 Then
       With ThisWorkbook.Worksheets(1)
                .Protect Password:="123india", UserInterfaceonly:=True, DrawingObjects:=False, Contents:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
                .Cells.Locked = False
                .Range("A1:E50").Locked = True
                .EnableSelection = xlNoRestrictions
                .EnableOutlining = True
      End With
    ElseIf ThisWorkbook.Sheets(2).Range("J1") = 2 Then
        With ThisWorkbook.Worksheets(1)
                .Protect Password:="123india", UserInterfaceonly:=True, DrawingObjects:=False, Contents:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
                .Cells.Locked = False
                .Range("A1:E50").Locked = True
                .EnableSelection = xlNoRestrictions
                .EnableOutlining = True
      End With
    End If

End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODySE9EM1FpN3ROc3c/edit?usp=sharing

Convert Excel file to *.CSV

Option Explicit
Dim rowcount As Long, filepath As String
Sub convertoCSV()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
    With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select a path to store file"
            If .Show = True Then
                filepath = .SelectedItems(1) & "\"
                'converting into text format
                ThisWorkbook.Sheets(1).Range("A:A").NumberFormat = "@"
                ThisWorkbook.SaveAs Filename:=filepath & "Somu.csv", FileFormat:=xlCSV, CreateBackup:=False
         
            Else
                MsgBox "Folder for *.CSV file not selected"
            End If
 
    End With


End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyOVZPSHUtSDBkZ2M/edit?usp=sharing

Cut & Paste using VBA Code

Task for attached link:
https://drive.google.com/file/d/0B23eJ2xd9ODyUEtOVk4zRWt4VDA/edit?usp=sharing

Want to cut all these HQ names from column B and paste them to the very next column C

VBA Code



Option Explicit
Dim lookupval As String, myrng As Range, firstaddress As String
Sub copyHOnamelist()

lookupval = "HQ Name:"
With ThisWorkbook.Sheets(1).Range("A:A")
Set myrng = .Find(lookupval, LookIn:=xlValues)

    
    If Not myrng Is Nothing Then
        firstaddress = myrng.Address
            Do
                
                myrng.Offset(0, 1).Cut Destination:=myrng.Offset(0, 2)
                Set myrng = .FindNext(myrng)
                
                     
        
                
            Loop While Not myrng Is Nothing And myrng.Address <> firstaddress
            
    End If
End With

End Sub

VBA Code to Split Word

Sl. No.	Name with No Space	Output
1	RahulSaxena	Rahul Saxena
2	PramodSingh	Pramod Singh
3	ManishSharma	Manish Sharma
4	DhirenMathur	Dhiren Mathur
5	RavindraKumarJena	Ravindra Kumar Jena
6	RamSinghTohan	Ram Singh Tohan
7	SurjitSinghManhas	Surjit Singh Manhas

Function splitWord(myval As String)

Dim tiny As String, finalword As String

finalword = myval

    For i = 1 To Len(myval)

        

        tiny = Mid(myval, i, 1)

            If (tiny = StrConv(tiny, vbUpperCase)) Then

            finalword = WorksheetFunction.Substitute(finalword, tiny, " " & tiny)

            

            End If

    Next

    splitWord = WorksheetFunction.Trim(finalword)

End Function

Create PivotTable with Criteria In VBA

Dim pvttable As PivotTable
Private Sub worksheet_change(ByVal target As Range)
    If target.Address = ThisWorkbook.Sheets("Soum").Range("C3").Address Then
        Select Case ThisWorkbook.Sheets("Soum").Range("C3")
            Case "Daily Report"
               Call createdailyReport
            Case "Weekly Report"
               Call createdweeklyReport
            Case "Monthly Report"
                Call createdmonthlyReport
 
        End Select
     
    End If
End Sub

Sub createdailyReport()
On Error Resume Next
Application.ScreenUpdating = False
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete

Next
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("Dump").Range("A2").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
        .PivotFields("Date").Orientation = xlRowField
        .PivotFields("Agent Name").Orientation = xlPageField
        '
        .PivotFields("ACD Calls").Orientation = xlDataField
     
        .PivotFields("Avg ACD Time").Orientation = xlDataField
        .PivotFields("Sum of ACD Calls").Caption = "Total ACD Calls"
        .PivotFields("Sum of Avg ACD Time").Caption = "Average ACD Time"


    End With
End Sub

Sub createdmonthlyReport()
On Error Resume Next
Application.ScreenUpdating = False
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete

Next
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("Dump").Range("A2").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
        .PivotFields("Date").Orientation = xlRowField
        Sheets("Soum").Range("C8").Select
        Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, False)
        .PivotFields("Agent Name").Orientation = xlPageField
        '.PivotFields("ACD Calls").Orientation = xlColumnField
        .PivotFields("ACD Calls").Orientation = xlDataField
     
        .PivotFields("Avg ACD Time").Orientation = xlDataField
        .PivotFields("Sum of ACD Calls").Caption = "Total ACD Calls"
        .PivotFields("Sum of Avg ACD Time").Caption = "Average ACD Time"
       ' .PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        'False, True, False, False)



    End With
End Sub

Sub createdweeklyReport()
'On Error Resume Next
Application.ScreenUpdating = False
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete

Next
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("Dump").Range("A2").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
        .PivotFields("Date").Orientation = xlRowField
        Sheets("Soum").Range("C8").Select
        Selection.Group Start:=True, End:=True, by:=7, Periods:=Array(False, _
        False, False, True, False, False, False)
        .PivotFields("Agent Name").Orientation = xlPageField
        '.PivotFields("ACD Calls").Orientation = xlColumnField
        .PivotFields("ACD Calls").Orientation = xlDataField
     
        .PivotFields("Avg ACD Time").Orientation = xlDataField
        .PivotFields("Sum of ACD Calls").Caption = "Total ACD Calls"
        .PivotFields("Sum of Avg ACD Time").Caption = "Average ACD Time"
       ' .PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        'False, True, False, False)



    End With
End Sub


For file:

https://drive.google.com/file/d/0B23eJ2xd9ODybUFMaEVYbnE4WDQ/edit?usp=sharing

Copy Specific Cells from files under Folder/SubFolder

Option Explicit
Dim objFso As Object, objFolder As Object, pathname As String, eachfile As Variant, objFolder1 As Object, eachfolder As Object
Dim tempcounter As Long, wb As Workbook, i As Integer, j As Integer


Sub copyFromSpecificfolderandsubfolder()
On Error Resume Next
pathname = "D:\Somu\ExcelClasses Notes"
Set objFso = New Scripting.FileSystemObject
Set objFolder = objFso.GetFolder(pathname)
Set objFolder1 = objFolder.SubFolders
Set wb = ThisWorkbook
tempcounter = 1
Call readfile(objFolder, tempcounter)

For Each eachfolder In objFolder1

Call readfile(eachfolder, tempcounter)

Next
tempcounter = 0
End Sub

Sub readfile(myFolder As Scripting.Folder, tempcounter)

For Each eachfile In myFolder.Files
    If checkExtension(eachfile) = True Then
        tempcounter = tempcounter + 1
 
        Workbooks.Open eachfile
        ActiveWorkbook.Sheets(1).Range("A2:C2").Copy wb.Sheets(1).Range("A" & tempcounter)
        ActiveWorkbook.Close
    End If
Next
End Sub

Public Function checkExtension(eachfile) As Boolean
i = InStr(1, eachfile, ".")
j = Len(eachfile)

Select Case Mid(eachfile, i + 1, (j - i))
    Case "xls"
        checkExtension = True
    Case "Xlsm"
        checkExtension = True
    Case "xlsx"
        checkExtension = True
    Case "xlsb"
        checkExtension = True
    Case Else
        checkExtension = False
End Select
End Function