Learn Visual Basic For Application - VBA

if you want to merge multiple excel files from a folder and specific sheet's data so you can use below code for merge files into one workbook.

Sub consolidation()
Dim s*t As Worksheet
Dim s*tName As String
Dim P As Long

Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet
Dim Searchs*t As String
Dim s*tsearch As Boolean
Dim activewkb As Workbook

Path = "D:\Daily work\WMS Data-Minutly - Copy (2)\Portfolio\"
Filename = Dir(Path & "*.xlsx") 'change excel format
Do While Filename ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

P = Sheets.Count

s*tName = InputBox(prompt:=Enter_sheet_Name, Title:="Search Sheet", Default:="1-MIN REPORT")

For P = 1 To P

If Sheets(P).Name = s*tName Then

'Exit Sub
End If
Next P

On Error Resume Next
ActiveWorkbook.Sheets(s*tName).Select
s*tsearch = (Err = 0)
On Error GoTo 0
ActiveSheet.Copy after:=Workbooks("codes.xlsm").Sheets(Workbooks("codes.xlsm").Sheets.Count)
Workbooks("codes.xlsm").Activate

ActiveSheet.Select
Range("j2:j30000").Value = Filename
Application.CutCopyMode = False

Workbooks(Filename).Close

Filename = Dir()
Loop
End Sub

Sub merge()
Dim i As Integer
Workbooks("codes.xlsm").Activate

For i = 1 To Worksheets.Count
Worksheets(i).Select
Range("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Book2").Activate
Sheets("sheet1").Select
Range("a1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues
'Application.CutCopyMode = False
Workbooks("codes.xlsm").Activate
Next i

End Sub

Hello guys,

You can ask any question regarding excel and vba on whatsapp.