coba ganti rng.value menjadi rng.text
Option Explicit
Sub gabung_sheet()
Application.ScreenUpdating = False
Dim wrk As Workbook
Dim sht As Worksheet
Dim trg As Worksheet
Dim rng As Range
Dim colCount As Integer
Set wrk = ActiveWorkbook
For Each sht In wrk.Worksheets
If sht.Name = "Hasil" Then
MsgBox "Worksheet 'Hasil' sudah ada ." & vbCrLf & _
"silahkan di delete dulu karena sheet 'Hasil' " & _
"akan menjadi hasil dari penggabugan ini.", vbOKOnly + vbExclamation, "Error"
Exit Sub
End If
Next sht
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
trg.Name = "Hasil"
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
.Font.Bold = True
End With
For Each sht In wrk.Worksheets
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
Set rng = sht.Range(sht.Cells(10, 1), sht.Cells(65536, 22).End(xlUp).Resize(, colCount))
'Ganti jadi text
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).text = rng.text
Next sht
trg.Columns.AutoFit
Application.ScreenUpdating = True
End Sub