VBA Copy and Paste data to another sheet(with a date and day)?

[mcmunoz]

Now, I have a major problem with copy and paste data. My data have some format that need to fulfill. With characters, date and day.

The output should be like this....

SEE the attachment below:
actualoutput.JPG

BUT I can't copy some of the data because some problem occur and not being copy and paste correctly.

Previous code: INCOMPLETE

Sub copyPasteData()
    
    Dim LResult As String
    
    i = 3
    n = 2
    
    For Each sh In Worksheets
        
        If sh.Name <> "Head Office" Then
        
            With sh.[A3]
            
            For k = 2 To .CurrentRegion.Columns.Count
                
               
                LResult = Left(sh.Name, 4)
                
                
                Sheets("Head Office").Cells(i, 1).Resize(24) = sh.Name
                Sheets("Head Office").Cells(i, 2).Resize(24) = LResult
                'Sheets("Head Office").Cells(i, 3).Resize(24) =
                
                Sheets("Head Office").Cells(i, 4).Resize(24) = .Columns(k).Value
                
                'Sheets("Head Office").Cells(i, 5).Resize(24) =
                
                Sheets("Head Office").Cells(i, 6).Resize(24) = Intersect(.CurrentRegion, .CurrentRegion.Offset(3).Columns(1)).Value
                Sheets("Head Office").Cells(i, 7).Resize(24) = .CurrentRegion.Offset(3).Columns(n).Value
                
                i = i + 24
                n = n + 1
 
            Next k
           
            End With
            
        End If
        
        n = 2
   
    Next sh
    
End Sub

Please see the attachment below:
actualoutput.JPG -> Need to be the OUTPUT


WORKBOOK:
Sample MQ Data.xlsb