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