How to insert data from an excel to another excel

[siamandm]

Hello All,

I have a finger machine for attendance records, which enables me to export the data to an excel file,
every month I have to change the format and organize the data.
if I have an excel sheet with a predefined format is there a way to insert data from the unorganized excel sheet into the well-formatted sheet?

excel.jpg

Regards

[HansV]

It would be possible to use a macro for that. It would help to have a small anonymized sample workbook.

[siamandm]

thank you for the reply, I have uploaded two files one is the data source and the other is the template

excel files.zip

Regards

[HansV]

The following macro will format the worksheet as in the template. Don't forget to save it as an Excel workbook.

Code: Select all

Sub TransformData()
    Dim varFile As Variant
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim r As Long
    Dim r0 As Long
    Dim strName As String
    varFile = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv")
    If varFile = False Then
        Beep
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Set wbk = Workbooks.Open(Filename:=varFile)
    Set wsh = wbk.Worksheets(1)
    wsh.UsedRange.Sort Key1:=wsh.Range("C1"), Key2:=wsh.Range("B1"), _
        Key3:=wsh.Range("D1"), Header:=xlYes
    r = 2
    Do
        If wsh.Range("C" & r).Value <> wsh.Range("C" & r - 1).Value Or _
                wsh.Range("B" & r).Value <> wsh.Range("B" & r - 1).Value Then
            strName = wsh.Range("B" & r).Value & " " & wsh.Range("C" & r).Value
            wsh.Range("A" & r).Resize(4).EntireRow.Insert
            If r0 > 0 Then
                wsh.Range("H" & r).Value = "Total:"
                wsh.Range("I" & r).Value = "=SUM(I" & r0 & ":I" & r - 1 & ")"
                wsh.Range("H" & r).Resize(1, 2).Interior.Color = 9359529
                wsh.Range("E" & r0 & ":I" & r).Borders.LineStyle = xlContinuous
            End If
            With wsh.Range("E" & r + 2)
                .Value = "Employee: " & strName
                .Font.Size = 12
                .Font.Bold = True
                .Interior.Color = 13285804
            End With
            wsh.Range("E" & r + 2).Resize(1, 5).Merge
            With wsh.Range("E" & r + 3).Resize(1, 5)
                .Value = Range("E1").Resize(1, 5).Value
                .Interior.Color = 11573124
            End With
            r0 = r
            r = r + 4
        End If
        r = r + 1
    Loop Until wsh.Range("A" & r).Value = ""
    ' Last range
    wsh.Range("H" & r).Value = "Total:"
    wsh.Range("I" & r).Value = "=SUM(I" & r0 & ":I" & r - 1 & ")"
    wsh.Range("H" & r).Resize(1, 2).Interior.Color = 9359529
    wsh.Range("E" & r0 & ":I" & r).Borders.LineStyle = xlContinuous
    wsh.Range("A1:A3").EntireRow.Delete
    wsh.Range("A1:D1").EntireColumn.Delete
    wsh.Range("A1:E1").ColumnWidth = 12
    Application.ScreenUpdating = True
End Sub