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

Split data of a Single workbook into different workbook

Option Explicit
Dim wb As Workbook, ws As Worksheet, myrng As Range, i As Integer, x As Integer
Dim fso As Object, fldr As Object, fl As Object, item_, itemcol As Collection
Dim path As String, rawdata
Sub migrateData()
Application.ScreenUpdating = False
On Error Resume Next
path = "D:\Somu\Task12092013-3"
 Set fso = New Scripting.FileSystemObject
 Set fldr = fso.GetFolder(path)
 Set itemcol = New Collection
 'For Each fl In fldr.Files
 Set wb = ThisWorkbook
 Set ws = wb.ActiveSheet
 MsgBox ws.UsedRange.Address
 Set myrng = ws.Range(ws.UsedRange.Address)
 item_ = ws.Range("d2:" & ws.Range("d2").End(xlDown).Address).Value
  For i = 1 To UBound(item_, 1)
        itemcol.Add item_(i, 1), item_(i, 1)

  Next
  For x = 1 To itemcol.Count
    myrng.AutoFilter , field:=4, Criteria1:=itemcol(x)
    rawdata = ws.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible)
        For Each fl In fldr.Files
               If Left(fl.Name, InStr(1, fl.Name, ".") - 1) = itemcol(x) Then
                Workbooks.Open path & "\" & fl.Name
                ActiveWorkbook.ActiveSheet.Range("a" & ActiveSheet.UsedRange.Rows.Count).Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
                ActiveWorkbook.Save
                ActiveWorkbook.Close
               Else
                    Workbooks.Add
                    ActiveWorkbook.ActiveSheet.Range("a1") = "City"
                    ActiveWorkbook.ActiveSheet.Range("b1") = "Map Code"
                    ActiveWorkbook.ActiveSheet.Range("c1") = "Model"
                    ActiveWorkbook.ActiveSheet.Range("d1") = "Country"
                    ActiveWorkbook.ActiveSheet.Range("e1") = "Batch Refference"
                    ActiveWorkbook.ActiveSheet.Range("f1") = "Source"
                    ActiveWorkbook.ActiveSheet.Range("a2").Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
                    ActiveWorkbook.SaveAs path & "\" & itemcol(x), FileFormat:=56
                    ActiveWorkbook.Close
                    Exit For
               End If
 
        Next
  Next
End Sub


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

A Simple VBA Code for Creating PivotTable

Dim pttbl As PivotTable
Sub createPivot()
On Error Resume Next
For Each pttbl In ThisWorkbook.Sheets(2).PivotTables
        ThisWorkbook.Sheets(2).Range(pttbl.TableRange2.Address).Delete
Next

   ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Address). _
   CreatePivotTable TableDestination:=ThisWorkbook.Sheets(2).Range("C5"), TableName:="PivotTable1"
    With ThisWorkbook.Sheets(2).PivotTables("PivotTable1")
        .PivotFields("Processed by").Orientation = xlRowField
        .PivotFields("status").Orientation = xlDataField
        .PivotFields("Transaction type").Orientation = xlPageField
     
    End With

End Sub




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

Sending Attachment through OutLook

Sub sendMail
Dim outApp As New Outlook.Application, outMail As Variant,mailId as String
Set outMail = outApp.CreateItem(olMailItem)
        With outMail
            .To = mailId
            .Subject = Mid(wb.Name, 1, InStr(wb.Name, ".") - 1)
            .Body = "Hi," & vbNewLine & "     Please find the attachment" & vbNewLine & "Regards," &        vbNewLine & "Soumyendu"
            .Attachments.Add (wb.Path & "\" & wb.Name)
         
            .Display
        End With
    wb.Close


End sub

Count Colored Cells through VBA

Option Explicit
Dim myrng As Range, rowcount As Integer, criteria As Range, cell As Variant, criteriaIndex As Long
Dim tempcounter As Long
Function countColoredcells(ByVal criteria As Range) As Long
rowcount = Sheets(3).Range("A1").End(xlDown).Row
Set myrng = ThisWorkbook.Sheets(3).Range("A1:A" & rowcount)
tempcounter = 0
    criteriaIndex = criteria.Interior.ColorIndex
    For Each cell In myrng
        If (cell.Interior.ColorIndex = criteriaIndex) Then
          tempcounter = tempcounter + 1
        End If
    Next
    countColoredcells = tempcounter
End Function

VBA Code for Autofilter with dynamic criteria

Option Explicit
Dim myrng As Range
Dim rowcount As Byte
Dim cell As Variant
Dim MyArray() As Variant, temp As Integer



Sub filterwithdynamicArray()
rowcount = Sheets(1).Range("H2").End(xlDown).Row
temp = 0

Set myrng = Sheets(1).Range("H2:H" & rowcount)
    For Each cell In myrng
        temp = temp + 1
        ReDim Preserve MyArray(1 To temp)
        MyArray(temp) = cell
    Next cell
    Sheets(1).Range("A1").AutoFilter field:=2, Criteria1:=MyArray, Operator:=xlFilterValues
End Sub

File to download:

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

CustomPercentile Function using VBA

Option Explicit
Option Base 1
Dim notemptyrng As Boolean
Function customPercentile(myrng As Range, myval As Double) As Variant
Dim Data() As Double
Dim i  As Long, j As Long, nearestRank As Long
    If isRangeEmpty(myrng) = False Then
            customPercentile = "Input Range is Empty"
            Exit Function
    End If
    If (myval < 0 Or myval > 100) Then
        customPercentile = "Percentile must be between 0& 100"
   
    End If
    ReDim Data(myrng.Rows.Count * myrng.Columns.Count)
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
                If IsNumeric(myrng.Cells(i, j)) = True Then
                    Data(i * j) = myrng.Cells(i, j)
                Else
                    customPercentile = "Error exists in" & myrng.Cells(i, j).Address
                    Exit Function
                End If
        Next
    Next
    Call myBubbleSort(Data)
    nearestRank = Round(myval / 100 * (myrng.Rows.Count * myrng.Columns.Count) + 0.5, 0)
   
    Select Case myval
        Case 0: customPercentile = Data(1)
        Case Else: customPercentile = Data(nearestRank)
    End Select
   
End Function

Function isRangeEmpty(myrng1 As Range) As Boolean
        notemptyrng = False
        If Application.WorksheetFunction.CountA(myrng1) > 0 Then
            notemptyrng = True
        End If
        isRangeEmpty = notemptyrng
End Function

Function myBubbleSort(myArray() As Double) As Variant
    Dim i As Long
    Dim j As Long
    Dim tempval As Variant
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
                If myArray(i) > myArray(j) Then
                tempval = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = tempval
                End If
        Next
    Next
End Function

Updating Master File Using VBA

VBA Code for updating Master file sheet by different name from other other workbooks with same sheet name under  folder( with same sheet name)




Option Explicit
Dim fso As FileSystemObject
Dim fl As file
Dim fldr As Folder, i As Integer
Dim wb As Workbook, rowcount As Integer, rowcount1 As Integer
Sub updateSheet()
On Error Resume Next
Application.ScreenUpdating = False
Set fso = New FileSystemObject
For i = 4 To ThisWorkbook.Sheets.Count
     
    If (fso.FolderExists(ThisWorkbook.Path & "\" & Sheets(i).Name) = True) Then
     
      Set fldr = fso.GetFolder(ThisWorkbook.Path & "\" & Sheets(i).Name & "\")
      rowcount = ThisWorkbook.Sheets(i).Range("a2").End(xlDown).Row
   
        For Each fl In fldr.Files
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & Sheets(i).Name & "\" & fl.Name)
            rowcount1 = wb.Sheets(1).Range("A2").End(xlDown).Row
         
            wb.Sheets(1).Range("A2:T" & rowcount1).Copy Destination:=ThisWorkbook.Sheets(i).Range("A" & rowcount)
            wb.Close
            Set wb = Nothing
        Next
    End If
Next
End Sub

Convert File Format in a Folder

VBA code for converting excel files to *.xls format in a folder

Option Explicit
Dim pathname As String, fso As New Scripting.FileSystemObject, folder, fl As Object
Sub renameFiles()

      With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select folder"
            If .Show = True Then
                pathname = .SelectedItems(1)

            Else
                MsgBox "Folder not selected"
            End If

      End With
      Set folder = fso.GetFolder(pathname)
   
      For Each fl In folder.Files
   
           If fso.GetExtensionName(pathname & "\" & fl.Name) Like "xl*" Then
                        fl.Name = "Somu.xls"
             
           End If
      Next
      Set folder = Nothing
      Set fso = Nothing
End Sub

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