Tried the below code with minor modification to select file referred from
Open 132 files and copy data into master file
My problem is its not copying the entire data. Copies only few 3 or 4 rows.
Please help
Open 132 files and copy data into master file
My problem is its not copying the entire data. Copies only few 3 or 4 rows.
Please help
Code:
Option Explicit
Public Sub CommandButton2_Click()
Dim Master As Workbook
Dim sourceBook As Workbook
Dim sourceData As Worksheet
Dim CurrentFileName As String
Dim myPath As String
Dim MyFile As Object
Dim Fileselected As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'The folder containing the files to be recap'd
myPath = "C:\Users\VSD\Desktop\Reports\"
Set MyFile = Application.FileDialog(msoFileDialogFilePicker)
With MyFile
.Title = "Choose File"
.AllowMultiSelect = False
If .Show <> -1 Then
Exit Sub
End If
Fileselected = .SelectedItems(1)
End With
'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(Fileselected)
'Create a workbook for the recap report
Set Master = ThisWorkbook
Do
Workbooks.Open (myPath & CurrentFileName)
Set sourceBook = Workbooks(CurrentFileName)
Set sourceData = sourceBook.Worksheets(2)
With sourceData
.Range("A5:FT" & Range("A" & Rows.Count).End(xlUp).Row).Copy Master.Worksheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With
sourceBook.Close
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""
MsgBox "Data Copied to Master DataBase-" & vbNewLine & "Done"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub