Merge All Files in a Folder be it Sheet name

sg2209

Board Regular
Joined
Oct 27, 2017
Messages
100
Office Version
  1. 2016
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Ron de Bruin

Active Member
Joined
Aug 1, 2006
Messages
271
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Oct 27, 2017
Messages
100
Office Version
  1. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top