Copy paste all excel files data in 1 excel sheet

[Abhijeet]

Hi

Please give me macro 1 folder all excel files copy paste data in 1 excel sheet.I have this macro but every files and range to define so i want what ever files those copy paste in 1 workbook and give summery for this which files data copy paste in this workbook

 

[Sathish KV]

Hi,

Try the below macro

Note: In a new file add the below macro and save the file in the same folder where the other excel files are there to consolidate

Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
 
Sub FILES_FROM_FOLDER()
 
    Dim msg As String
    Dim title As String
    Dim style As VbMsgBoxStyle
    Dim response As VbMsgBoxResult
   
    msg = "Do you want to Consolidate Excel Macro?"
 
    style = VbMsgBoxStyle.vbYesNo
    title = "Message"
   
    response = MsgBox(msg, style, title)
    If response = vbYes Then
 
Application.Calculation = xlCalculationManual
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
ToBook = ActiveWorkbook.Name
 
Set ToSheet = ActiveSheet
NumColumns = ToSheet.Range("a1").End(xlToRight).Column
ToRow = ToSheet.Range("b65536").End(xlUp).Row
 
If ToRow <> 1 Then
ToSheet.Range(ToSheet.Cells(2, 1), _
ToSheet.Cells(ToRow, NumColumns)).ClearContents
End If
ToRow = 2
 
FromBook = Dir("*.xlsx")
While FromBook <> ""
If FromBook <> ToBook Then
Application.StatusBar = FromBook
Transfer_data
End If
FromBook = Dir
Wend
 
MsgBox ("Done.")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
 
End If
End Sub
 
Private Sub Transfer_data()
Workbooks.Open Filename:=FromBook
For Each FromSheet In Workbooks(FromBook).Worksheets
LastRow = FromSheet.Range("b65536").End(xlUp).Row
 
FromSheet.Range(FromSheet.Cells(1, 1), _
FromSheet.Cells(LastRow, NumColumns)).Copy _
Destination:=ToSheet.Range("A" & ToRow)
 
 
ToRow = ToSheet.Range("b65536").End(xlUp).Row + 1
Next
Workbooks(FromBook).Close savechanges:=False
 
End Sub

 

[Abhijeet]

Thanks for macro but no summery data i want which file this data come i want this info also

 

[Sathish KV]

Hi,

Could you post a sample of the summary you are expecting

 

[Abhijeet]

In Column A which file copy paste & column B which sheet Data that info i want