Combine Multiple worksheets in single worksheet with in workbook

Sub Mtest()
Dim i As Integer
Dim ws As Worksheet
Worksheets(Worksheets.Count).Activate
For i = 1 To Worksheets.Count
'Change sheet name where you want to copy data from all other sheets
If Sheets(i).Name <> "Mainsheet" Then
'change you range here
Sheets(i).Range("A1:A25").Copy Destination:=Sheets("Mainsheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
'OR if header of all sheets are common  & copy used rows
'Sheets(i).UsedRange.Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Copy Destination:=Sheets("Mainsheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

Extract File name from path & file name

I have already posted how to find the content of folder with vb
if you you want to separate file name from file path & file name then use below formula

suppose your cell A1 contain C:\Users\MAHESH\Downloads\Data sample.xls
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"\","*",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))))
results will be Data sample.xls

1.  Find total len of cell   LEN(A1) =41
2. find Len of cell without "\" using SUBSTITUTE    LEN(SUBSTITUTE(A1,"\","") =37
3. (1-2) will give u count of number of backslash  "\" i.e (41-37=4)
4. we got last position of bracket is 4, but don’t know the string position in cell
5. Find function always find the first instance
6. Use the SUBSTITUTE function to change the last backslash
7. FIND("*",SUBSTITUTE(A1,"\","*",4) will give you position of last backslash i.e 26
8. we are replacing last back slash as * to get the position of last backslash
9. we have taken 4 (number of backslash) as  instance_num in substitute formula
10. Use Right function which will give you file name

You can try below DOS command to directory of the folder & sub folder
c:\User> Dir/s/b > F:\file.txt
this will save all the list of file (Directory) in F drive in file.txt you can use file.xls to save output as excel file

Search String/value in Entire workbook : Return worksheet Name & cell Address

Try :

Sub Mtest()
Dim found As Range
Dim m As String
Dim count As Integer
Dim ws As Worksheet
count = 0
m = InputBox("Enter the Search")
For Each ws In ActiveWorkbook.Worksheets
Set found = ws.Cells.Find(What:=m, LookIn:=xlValues, lookat:=xlWhole)
If Not found Is Nothing Then
count = count + 1
MsgBox found.Worksheet.Name & found.Cells.Address
End If
Next ws
If count = 0 Then MsgBox "No Matches Found"
End Sub

Pivot Table : After double click Change Format of New Sheet to normal

Copy below code in thisworkbook model

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Dim sht As Worksheet
    Dim oLo As ListObject
    Set sht = ActiveSheet
    For Each oLo In sht.ListObjects
    sht.ListObjects(oLo.Name).TableStyle = ""
    Next
End Sub