Excel vba code to copy data in multiple selected files

suyogpat

New Member
Joined
Jun 29, 2017
Messages
28
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.

VBA Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I hope that this helps. I apologize for the klunky code. That is my syle: klunky. Perhaps someone has a better solution. Good Luck!

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top