Excel vba code to copy data in multiple selected files

[suyogpat]

Hi All,

I am using below macro code where a dialouge box is opening to select multiple excel files.
Next step i am looking for is macro to repeat the highlighted code {Starts from column("E:E")} activity for all the files I have selected. can you guys help me with same. May be a loop is needed here but I am not that good with VBA yet. Appreciate your help on this.

sub billingstatus () 
Dim i as Integer 
'opening file dialog box
With application. FileDialog(msofiledialogfilepicker) 
  ' enabling multiple files select
  . Allowmultiselect= True 
  . Filters. Clear
'only excel files can be selected
. FiltersAdd "excel files", "*. Xslx*" 

If. Show = True then 
     For i = 1 to. Selecteditems(i) . Count
   ' opening file
    Workbooks. Open. SelectedItems(i) 
Next i
End if 

End With

[B]Columns ("E:E"). Select
Selection.numberformat = "0"
Activeworkbook.pivotcaches.create(sourcetype:=xldatabase, sourcedata:=
"Sheet1! R1C1:R 32676",version :=6). Createpivottable tabledestination:="_, 
Tablename:=" pivottable3", defaultversion:=6
With Activesheet. Pivottables("pivottable3") 
         Ingriddropzones = True
End with
Activeworkbook. Close[/B]
End sub

 

[OaklandJim]

I hope that this helps. I apologize for the klunky code. That is my syle: klunky. Perhaps someone has a better solution. Good Luck!

Sub OpenAndProcessesFiles()

    Dim wbTarget As Workbook

    Dim wsSource As Worksheet
   
    Dim wsTarget As Worksheet
   
    Dim rPivotTableLocation As Range
   
    Dim rPivotTableData As Range
   
    Dim sDirectory As String
   
    Dim sFileName As String
   
    Dim sPathToTargetFiles As String
   
    Dim sPivotTableName As String
   
    Dim iFile As Long
   
    Dim oFileDialog As Office.FileDialog
   
    Dim ptPivotTable3 As PivotTable
   
'   Where files to open are located (folder)
    sPathToTargetFiles = "C:\Users\Bob\Desktop\" 
   
    sPivotTableName = "PivotTable3"
   
'   If needed, set the source worksheet with the data to put into worksheets that are opened.
    Set wsSource = ThisWorkbook.Worksheets("Sheet1")

    Set oFileDialog = Application.FileDialog(msoFileDialogFilePicker)

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    With oFileDialog
        .AllowMultiSelect = True
        .Title = "Please select the files."
        .Filters.Clear
       
'       Specify the initial path where target files are located.
        .InitialFileName = sPathToTargetFiles
       
'       Show only Excel .xlsx files in the file picker.
        .Filters.Add "Excel Files", "*.xlsx?"
       
'       Show the dialog box.
        .Show
       
        If .SelectedItems.Count <> 0 _
         Then
'           Iterate through files to process -- the ones that user selected.
            For iFile = 1 To .SelectedItems.Count
                sFileName = Dir(.SelectedItems(iFile))
               
'               Open the workbook to process. wbTarget object points to it
                Set wbTarget = Workbooks.Open(sPathToTargetFiles & sFileName)
               
'               Point wsTarget object to the sheet to modify.
                Set wsTarget = wbTarget.Worksheets("Sheet2")
               
                On Error Resume Next
                wsTarget.PivotTables(sPivotTableName).TableRange2.Clear
                On Error GoTo 0
               
'               Location of the pivot table that will be created.
                Set rPivotTableLocation = wsTarget.Range("F1")

'               Location of data for pivot table
                Set rPivotTableData = wsTarget.Range("A3:D7")

'               Add the pivot table to the specified location with the specified name.
                wbTarget.PivotCaches.Create( _
                    SourceType:=xlDatabase, _
                    SourceData:=rPivotTableData, _
                    Version:=6) _
                       .CreatePivotTable _
                            TableDestination:=rPivotTableLocation, _
                            TableName:=sPivotTableName, _
                            DefaultVersion:=6
   
                With wsTarget.PivotTables(sPivotTableName)
                    .InGridDropZones = True
                End With
               
'               Format column E to Format "0"
                wsTarget.Range("E:E").NumberFormat = "0"
   
'               Close the file after processing
                wbTarget.Close SaveChanges:=True
           
            Next iFile
       
        End If '.SelectedItems.Count <> 0
   
  End With 'oFileDialog

End Sub