Sub zCleaner()
 'Saves original and sets new decimal separator setting
    OriginalDecimalSeparator = Application.DecimalSeparator
    OriginalThousandsSeparator = Application.ThousandsSeparator
    OriginalUsedSystemseparators = Application.UseSystemSeparators
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ","
    Application.UseSystemSeparators = False
 'Opens the z-tree .xls-file
   With Application.FileDialog(msoFileDialogOpen)
      .AllowMultiSelect = False
      .Show
      zFileName = .SelectedItems(1)
   End With
   Workbooks.OpenText Filename:=zFileName, Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
        False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
  'Checks for obvious signs that this is not an original z-tree .xls output file
    If Sheets.Count > 1 Or ActiveSheet.Name <> Range("A1").Value Then
       a = MsgBox("Please use this macro on the original z-tree output file only!", vbOKOnly, "Wrong file")
       Application.UseSystemSeparators = OriginalUsedSystemseparators
       Exit Sub
    End If
    a = MsgBox("Should column headings be copied for each new period?", vbYesNoCancel, "Column headings")
    If a = vbCancel Then
       Application.UseSystemSeparators = OriginalUsedSystemseparators
       Exit Sub
    End If
    OnlyFirstHeadings = 0
    If a = vbNo Then OnlyFirstHeadings = 1
 'Renames z-tree output sheet to RawData
    ActiveWorkbook.ActiveSheet.Name = "RawData"
 'Inserts Info sheet and tints tab
    Call zAddSheet(Sheets.Count, "Info", xlThemeColorLight1, 0)
 'Calculates number of lines in RawData and outputs into Info
    Range("A1").FormulaR1C1 = "RawData_TotalLines"
    Range("B1").FormulaR1C1 = "=COUNTA(RawData!C[-1])"
 'Prepares Info for table list
    Range("C1").FormulaR1C1 = "RawData_Tables"
    Range("C2").FormulaR1C1 = "=COUNTA(R[1]C:R[1000]C)"
    RawDataNumLines = Cells(1, 2).Value
    Columns("A:A").EntireColumn.AutoFit
 'Writes session date and time into Info
    Range("A2").FormulaR1C1 = "SessionDate"
    Range("A3").FormulaR1C1 = "SessionTime"
    Range("B2").FormulaR1C1 = "=DATE(IF(VALUE(LEFT(RawData!R1C1,2))<30,2000,0)+VALUE(LEFT(RawData!R1C1,2)),VALUE(RIGHT(LEFT(RawData!R1C1,4),2)),VALUE(LEFT(RIGHT(RawData!R1C1,7),2)))"
    Range("B3").FormulaR1C1 = "=TIME(VALUE(LEFT(RIGHT(RawData!R1C1,4),2)),VALUE(RIGHT(RawData!R1C1,2)),0)"
 'Writes all Tables into Info
    Sheets("RawData").Select
    NumTables = 0
    For r = 2 To RawDataNumLines
       Call zAddTable(Cells(r, 3).Value)
    Next r
 'Creates output sheets
    For r = 3 To 2 + NumTables
       Sheets("Info").Select
       Call zAddSheet(Sheets.Count - 1, Cells(r, 3).Value, xlThemeColorLight2, 0.599993896298105)
    Next r
  'Fills output sheets
    For r = 3 To 2 + NumTables
       Sheets("Info").Select
     'Writes current table name to be processed into TableName
       TableName = Cells(r, 3).Value
       Outputrow = 1 'Current output row in the target table's output sheet
       FirstOccurrence = 1 'True as long as only first occurrence of a table has been copied to output sheet
       For RawDataRow = 1 To RawDataNumLines
          Sheets("RawData").Select
        'Searches for next occurrence of current table name, then looks for last row of current table block
        'and copies the block to output sheet
          If Cells(RawDataRow, 3).Value = TableName Then
             For i = RawDataRow To RawDataNumLines + 1
                If Cells(i, 3).Value <> TableName Then
                   Endrow = i - 1
                   Exit For
                End If
             Next i
           'Shifts RawDataRow by 1 if first occurrence of this table name, so column headings are copied only once
             Range(Rows(RawDataRow + (1 - FirstOccurrence)), Rows(Endrow)).Select
             Selection.Copy
             Sheets(TableName).Select
             Cells(Outputrow, 1).Select
             ActiveSheet.Paste
           'MoveOutputrow to current outputrow + length of block just added plus 1
             Outputrow = Outputrow + Endrow - RawDataRow - (1 - FirstOccurrence) + 1
             If FirstOccurrence And OnlyFirstHeadings Then FirstOccurrence = 0
           'Move RawEndRow to the Endrow plus 3 (+1 for row after Endrow, +2 because we know the row after Endrow
           'contains a different table name and because each table block has a minimum length of 2, -1 because next
           'FOR increases RawEndRow by 1)
             RawDataRow = RawDataRow + (Endrow - RawDataRow) + 1
          End If
       Next RawDataRow
    Next r
  'Deletes first row
    For SheetNum = 2 To Sheets.Count - 1
       Columns("A:A").Delete Shift:=xlToLeft
       Cells(1, 1).Select
    Next SheetNum
    Sheets("Info").Select
    Application.UseSystemSeparators = OriginalUsedSystemseparators
End Sub



Sub zAddTable(TableName As String)
   Exists = False
   Sheets("Info").Select
 'Searches for TableName in list of existing tables
   For r = 3 To 3 + NumTables
      If Cells(r, 3).Value = TableName Then Exists = True
   Next r
 'Adds TableName if it is not yet in list
   If Not Exists Then
      NumTables = NumTables + 1
      Cells(2 + NumTables, 3).Value = TableName
   End If
   Sheets("RawData").Select
End Sub



Sub zAddSheet(Position As Integer, SheetName As String, SheetColor As Integer, SheetTint As Double)
  'Inserts sheet and tints tab
    Sheets.Add After:=Sheets(Position)
    ActiveWorkbook.Sheets(Position + 1).Name = SheetName
    With ActiveWorkbook.Sheets(Position + 1).Tab
        .ThemeColor = SheetColor
        .TintAndShade = SheetTint
    End With
End Sub
