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?
Regards
How to insert data from an excel to another excel
-
- BronzeLounger
- Posts: 1226
- Joined: 01 May 2016, 09:58
How to insert data from an excel to another excel
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78464
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to insert data from an excel to another excel
It would be possible to use a macro for that. It would help to have a small anonymized sample workbook.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1226
- Joined: 01 May 2016, 09:58
Re: How to insert data from an excel to another excel
thank you for the reply, I have uploaded two files one is the data source and the other is the template
RegardsYou do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78464
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to insert data from an excel to another excel
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
Best wishes,
Hans
Hans