Selamat siang suhu-suhu excel,

saya mau menggabungkan beberapa file excel pada sheet tertentu menjadi satu sheet excel, namun kesulitan caranya.(terlampir file yang akan di merge yaitu pada sheet "Primary Aluminium" yang akan dimerge ke file "merge"), saya mencoba menggunakan coding ini namun tidak bisa.
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\SHARE ON P1-PUD04S\My Data\New Job PUD#1\IDP\trial merge doc")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A1048576" to the same column as start point
Range("B9:IV" & Range("A1048576").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(4).Activate

'Do not change the following column. It's not the same column as above
Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Terimakasih banyak suhu @manweljs_ akan saya pelajari
codingnya..sangat bermanfaat sekali...

@manweljs_

setelah coba saya aplikasikan ke file dengan extend file xlsm dengan sheet yang diprotect kenapa tidak berfungsi ya..

Mohon pencerahannya..

@manweljs_

agar hasil paste menjadi paste value ditambahkan .PasteSpecial xlPasteValues nya dimana ya??

Sub Tesaja()
Dim Wb, xWb, Sh, xSh, p, fname
Set Wb = ThisWorkbook
Set Sh = Wb.Sheets("Sheet1")
p = ThisWorkbook.Path
fname = Dir(p & "\*.xlsx")
On Error Resume Next

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

Do While Len(fname)
Set xWb = Workbooks.Open(p & "\" & fname)
Set xSh = xWb.Sheets("CEPT Data")
xSh.Range("A9:AJ" & r(xSh)).Copy Sh.Range("A" & r(Sh) + 1)
xWb.Close
fname = Dir()
Loop

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

Err.Clear
End Sub

Function r(ByVal s As Worksheet)
r = s.Range("A" & Rows.Count).End(xlUp).Row
End Function