Merge All Files in a Folder be it Sheet name

[sg2209]

Dear Mentors,
Hope you all are Doing Good !!

After viewing so much videos and solution , i am able to combine all the workbooks in a folder.
This code merge all the workbook in a folder names source folder. My issue is as of now this is only collate the Sheet named Data, and now sheets names are getting changed Data , Data 1 , Data2 Etc.

how could i made changes so this should collate all the sheets

Sub LoopThroughDirectory()
Dim MyFile As String
Dim MyPath As String
Dim Wbk As Workbook
Dim DestSht As Worksheet

Set DestSht = ThisWorkbook.Sheets("Raw")
MyPath = "C:\Users\god\Desktop\Source Folder\" 'please change pat as per teh need
MyFile = Dir(MyPath)

Do While Len(MyFile) > 0
If MyFile <> "Collated.xlsm" Then

Set Wbk = Workbooks.Open(MyPath & MyFile)
Wbk.Sheets("Data").Range("A2:D5000").Copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(1)

Wbk.Close False
End If

MyFile = Dir
Loop
'Freezing the Top Row
Rows("2:2").Select
ActiveWindow.FreezePanes = True

End Sub

Please help me

Also is it possible in Sheet2 Cell B1 i can put the source location and in Cell B2 i can put the destination location, so i should not always change my codes.


Thanks for thehelp

 

[Ron de Bruin]

My Merge Add-in have a option for all worksheets

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

 

[sg2209]

My Merge Add-in have a option for all worksheets

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

Thank You So much Ron Sir for this add in . I have dowloaded this.
but if i need his for my colleagues and if in another sheet like we have the opion so anyone can put the location in Source and destination, is it possible though vba

 

[Ron de Bruin]

I have a lot of code on this page : Merge data from all workbooks in a folder
See also the FSO code in the TXT file, easy to add the loop through all sheets in the workbook.

If you need more help post back

 

[sg2209]

I have a lot of code on this page : Merge data from all workbooks in a folder
See also the FSO code in the TXT file, easy to add the loop through all sheets in the workbook.

If you need more help post back

Ron Sir i am getting hard time ammending this

 

[Ron de Bruin]

That's why I create the Add-in to make it easy

Tell me exact in what part of the code you have problems and we can help you I am sure

 

[sg2209]

That's why I create the Add-in to make it easy

Tell me exact in what part of the code you have problems and we can help you I am sure

That's why I create the Add-in to make it easy

Tell me exact in what part of the code you have problems and we can help you I am sure

It was thrwoing an error that sheet name is already exits, but i am using this add in.

Thanks much