Merge All Files in a Folder be it Sheet name

sg2209

Board Regular
Joined
Oct 27, 2017
Messages
117
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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
Back
Top