Option Explicit
Sub Prepare_Data()

Dim ForceClose As Boolean
ForceClose = False

    Prepare_Raw_Data ForceClose
        If ForceClose = True Then Exit Sub
    Prepare_Speed_Data
    Prepare_Speed_Graph
    Prepare_VANOS_Data
    Prepare_VANOS_Exhaust_Chart
    Prepare_VANOS_Inlet_Chart
    Prepare_Temperatures_Data
    Prepare_Temperatures_Chart
    Prepare_Air_Flow_Data
    Prepare_Air_Flow_Chart
    Prepare_Shut_Off_Cyl_Data
    Prepare_Shut_Off_Cyl_Chart
    Prepare_Ignition_Data
    Prepare_Ignition_Angle_Chart
    Prepare_Injection_Time_Chart
    Prepare_Injection_Time_Difference_Chart
    
    Sheets(Sheets(Sheets.Count).Name).Select
    Range("C4").Select

    MsgBox "Data imported and graphs created"

End Sub

Private Sub Prepare_Raw_Data(ForceClose As Boolean)

On Error GoTo ErrorHandler_01     ' Enable error-handling routine if the file is open

Dim FullFileCSVNamePath As String
Dim FullFileLength As Long
Dim x As String
Dim FolderSlashNumber As Long
Dim CSVFileName As String
Dim CSVFolderName As String
Dim FileNameLength As Long
Dim Sheet1Name As String
Dim intNumberOfLinesToCopy As Long
Dim strNumberOfLinesToCopy As String
Dim h As Integer
Dim m As Integer
Dim s As Integer
Dim intColumnNumber As Integer
Dim strColumnLetter As String

' Select the CSV file to interrogate and open it
    FullFileCSVNamePath = Application.GetSaveAsFilename( _
        fileFilter:="CSV Files (*.csv), *.csv")
        
    If FullFileCSVNamePath = "False" Then ForceClose = True: Exit Sub

    FullFileLength = Len(FullFileCSVNamePath)
    FolderSlashNumber = FullFileLength
    
    Do Until x = "\"
        x = Mid(FullFileCSVNamePath, FolderSlashNumber, 1)
        FolderSlashNumber = FolderSlashNumber - 1
    Loop
    CSVFileName = Mid(FullFileCSVNamePath, FolderSlashNumber + 2, FullFileLength - FolderSlashNumber)
    CSVFolderName = Left(FullFileCSVNamePath, FolderSlashNumber + 1)
    
    OpenWorkbook CSVFolderName, CSVFileName
    
' Copy the data from the csv file
    Windows(CSVFileName).Activate
    Range("A2:AH2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

' Paste the data into this workbook
    Windows(ThisWorkbook.Name).Activate
    Sheets("Sheet1").Select
    Range("B4").Select
    ActiveSheet.Paste
    Range("A1").Select
' A cheeky line to copy a single cell.  This avoids a "large amount of data on clipboard" msg
    Selection.Copy
    
' Close the csv file without saving any changes
    Windows(CSVFileName).Close False

'Rename Sheet1
    FileNameLength = Len(CSVFileName)
    Sheet1Name = Left(CSVFileName, FileNameLength - 4)
    Sheets("Sheet1").Select
On Error GoTo ErrorHandler_01     ' Enable error-handling routine if the sheet name is too long
    Sheets("Sheet1").Name = Sheet1Name
    

' Count number of lines
    intNumberOfLinesToCopy = Sheets(Sheet1Name).Range("B1").End(xlDown).Row
    strNumberOfLinesToCopy = intNumberOfLinesToCopy

' Complete the Min & Max values cells
    Sheets(Sheet1Name).Range("C1").Formula = "=MIN(C4:C" & strNumberOfLinesToCopy & ")"
    Sheets(Sheet1Name).Range("C2").Formula = "=MAX(C4:C" & strNumberOfLinesToCopy & ")"
    Sheets(Sheet1Name).Range("C1:C2").AutoFill Destination:=Range("C1:AI2")
    
' Format Time Column to show hours, minutes, seconds and tenths of seconds
    Columns("B:B").Select
    Selection.NumberFormat = "hh:mm:ss.0;@"
    Columns("B:B").EntireColumn.AutoFit
    Range("C4").Select
    
' Build formula to start time from zero
    h = Hour(Abs(Range("B4").Value))
    m = Minute(Abs(Range("B4").Value))
    s = Second(Abs(Range("B4").Value - (0.5 / 60 / 60 / 24)))
    Range("A4").Formula = "=B4-TIME(" & h & "," & m & "," & s & ")"
    Range("A4").Select
    Selection.AutoFill Destination:=Range("A4:A" & strNumberOfLinesToCopy)
    
' Populate A1 with first line
    Range("A1").Value = 4
    Range("A2").Value = intNumberOfLinesToCopy
    
Exit Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

ErrorHandler_01:
    Select Case Err.Number
        Case 1004                              ' "Sheet name too long" error.
            Sheet1Name = Left(Sheet1Name, 17)
            Resume
        Case Else
        
        ' Handle other situations here....

    End Select

Resume              ' Resume execution at same line that caused the error.
    
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

Private Sub OpenWorkbook(Directory As String, FileName As String)

Dim File As String, _
    CheckForFile As String

    File = Directory & FileName
    
' If the file exists, then open it
    CheckForFile = Dir(File)
    If UCase(CheckForFile) <> UCase(FileName) Then
        MsgBox "'" & FileName & "' doesn't exist.", _
                    vbOKOnly + vbInformation, _
                    "Open File"
        Exit Sub
    Else
        On Error GoTo ErrorHandler     ' Enable error-handling routine if the file is open
        Workbooks.Open Directory & FileName
    End If

Exit Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

ErrorHandler:
    Select Case Err.Number
        Case 70                              ' "File already open" error.
            MsgBox "Close any open versions of '" & FileName & "'" & _
                            Chr(13) & Chr(13) & _
                            "       and re-run the macro.", _
                    vbOKOnly + vbInformation, _
                    "Delete File"
            Exit Sub
            
        Case Else
        
        ' Handle other situations here....

    End Select

Resume              ' Resume execution at same line that caused the error.
    
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

Private Sub Prepare_Speed_Data()

Dim StartRow As Long
Dim FinishRow As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    StartRow = Sheets(RawDataSheetName).Range("A1").Value
    FinishRow = Sheets(RawDataSheetName).Range("A2").Value
    
    
    Sheets("Speed Data").Select

    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Range("A4").Formula = "='" & RawDataSheetName & "'!A" & StartRow
    Range("B4").Formula = "='" & RawDataSheetName & "'!U" & StartRow
    Range("C4").Formula = "='" & RawDataSheetName & "'!J" & StartRow
    Range("D4").Formula = "=B" & StartRow & "/100"
    Range("E4").Formula = "=C" & StartRow & "/1.608"
    Range("F4").Formula = "='" & RawDataSheetName & "'!G" & StartRow
    Range("G4").Formula = "='" & RawDataSheetName & "'!X" & StartRow
    Range("H4").Formula = "=G" & StartRow & "*10"
    
    Range("A4:H4").Select
    Selection.AutoFill Destination:=Range("A4:H" & FinishRow), Type:=xlFillDefault
    
    Range("A4").Select
    
End Sub

Private Sub Prepare_Speed_Graph()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Speed Graph").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='Speed Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='Speed Data'!$D$" & FirstLine & ":$D$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='Speed Data'!$E$" & FirstLine & ":$E$" & LastLine
    ActiveChart.SeriesCollection(3).Values = "='Speed Data'!$F$" & FirstLine & ":$F$" & LastLine
    ActiveChart.SeriesCollection(4).Values = "='Speed Data'!$H$" & FirstLine & ":$H$" & LastLine

End Sub

Private Sub Prepare_VANOS_Data()

Dim StartRow As Long
Dim FinishRow As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    StartRow = Sheets(RawDataSheetName).Range("A1").Value
    FinishRow = Sheets(RawDataSheetName).Range("A2").Value
    
    
    Sheets("VANOS Data").Select
    
    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Range("A4").Formula = "='" & RawDataSheetName & "'!A" & StartRow
    Range("B4").Formula = "='" & RawDataSheetName & "'!U" & StartRow
    Range("C4").Formula = "=B" & StartRow & "/100"
    Range("D4").Formula = "=B" & StartRow & "/1000"
    Range("E4").Formula = "='" & RawDataSheetName & "'!F" & StartRow
    Range("F4").Formula = "='" & RawDataSheetName & "'!E" & StartRow
    Range("G4").Formula = "='" & RawDataSheetName & "'!I" & StartRow
    Range("H4").Formula = "='" & RawDataSheetName & "'!H" & StartRow
    
    Range("A4:H4").Select
    Selection.AutoFill Destination:=Range("A4:H" & FinishRow), Type:=xlFillDefault
    
    Range("A4").Select
    
End Sub

Private Sub Prepare_VANOS_Exhaust_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Exhaust VANOS Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='VANOS Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='VANOS Data'!$C$" & FirstLine & ":$C$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='VANOS Data'!$E$" & FirstLine & ":$E$" & LastLine
    ActiveChart.SeriesCollection(3).Values = "='VANOS Data'!$F$" & FirstLine & ":$F$" & LastLine

End Sub

Private Sub Prepare_VANOS_Inlet_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Inlet VANOS Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='VANOS Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='VANOS Data'!$D$" & FirstLine & ":$D$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='VANOS Data'!$G$" & FirstLine & ":$G$" & LastLine
    ActiveChart.SeriesCollection(3).Values = "='VANOS Data'!$H$" & FirstLine & ":$H$" & LastLine

End Sub

Private Sub Prepare_Temperatures_Data()

Dim StartRow As Long
Dim FinishRow As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    StartRow = Sheets(RawDataSheetName).Range("A1").Value
    FinishRow = Sheets(RawDataSheetName).Range("A2").Value
    
    
    Sheets("Temperatures Data").Select
    
    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Range("A4").Formula = "='" & RawDataSheetName & "'!A" & StartRow
    Range("B4").Formula = "='" & RawDataSheetName & "'!C" & StartRow
    Range("C4").Formula = "='" & RawDataSheetName & "'!V" & StartRow
    Range("D4").Formula = "='" & RawDataSheetName & "'!W" & StartRow
    
    Range("A4:D4").Select
    Selection.AutoFill Destination:=Range("A4:D" & FinishRow), Type:=xlFillDefault
    
    Range("A4").Select
    
End Sub

Private Sub Prepare_Temperatures_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Temperatures Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='Temperatures Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='Temperatures Data'!$B$" & FirstLine & ":$B$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='Temperatures Data'!$C$" & FirstLine & ":$C$" & LastLine
    ActiveChart.SeriesCollection(3).Values = "='Temperatures Data'!$D$" & FirstLine & ":$D$" & LastLine

End Sub

Private Sub Prepare_Air_Flow_Data()

Dim StartRow As Long
Dim FinishRow As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    StartRow = Sheets(RawDataSheetName).Range("A1").Value
    FinishRow = Sheets(RawDataSheetName).Range("A2").Value
    
    
    Sheets("Air Flow Data").Select
    
    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Range("A4").Formula = "='" & RawDataSheetName & "'!A" & StartRow
    Range("B4").Formula = "='" & RawDataSheetName & "'!U" & StartRow
    Range("C4").Formula = "='" & RawDataSheetName & "'!AB" & StartRow
    Range("D4").Formula = "=B" & StartRow & "/100"
    Range("E4").Formula = "='" & RawDataSheetName & "'!G" & StartRow
    Range("F4").Formula = "=C" & StartRow & "/10"
    
    Range("A4:F4").Select
    Selection.AutoFill Destination:=Range("A4:F" & FinishRow), Type:=xlFillDefault
    
    Range("A4").Select
    
End Sub

Private Sub Prepare_Air_Flow_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Air Flow Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='Air Flow Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='Air Flow Data'!$D$" & FirstLine & ":$D$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='Air Flow Data'!$E$" & FirstLine & ":$E$" & LastLine
    ActiveChart.SeriesCollection(3).Values = "='Air Flow Data'!$F$" & FirstLine & ":$F$" & LastLine

End Sub

Private Sub Prepare_Shut_Off_Cyl_Data()

Dim StartRow As Long
Dim FinishRow As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    StartRow = Sheets(RawDataSheetName).Range("A1").Value
    FinishRow = Sheets(RawDataSheetName).Range("A2").Value
    
    
    Sheets("Shut-Off Cyl Data").Select
    
    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Range("A4").Formula = "='" & RawDataSheetName & "'!A" & StartRow
    Range("B4").Formula = "='" & RawDataSheetName & "'!U" & StartRow
    Range("C4").Formula = "='" & RawDataSheetName & "'!J" & StartRow
    Range("D4").Formula = "=B" & StartRow & "/1000"
    Range("E4").Formula = "=C" & StartRow & "/1.608/10"
    Range("F4").Formula = "='" & RawDataSheetName & "'!D" & StartRow
    Range("G4").Formula = "='" & RawDataSheetName & "'!G" & StartRow
    Range("H4").Formula = "=G" & StartRow & "/10"
    
    Range("A4:H4").Select
    Selection.AutoFill Destination:=Range("A4:H" & FinishRow), Type:=xlFillDefault
    
    Range("A4").Select
    
End Sub

Private Sub Prepare_Shut_Off_Cyl_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Shut-Off Cyl Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='Shut-Off Cyl Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='Shut-Off Cyl Data'!$D$" & FirstLine & ":$D$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='Shut-Off Cyl Data'!$E$" & FirstLine & ":$E$" & LastLine
    ActiveChart.SeriesCollection(3).Values = "='Shut-Off Cyl Data'!$F$" & FirstLine & ":$F$" & LastLine
    ActiveChart.SeriesCollection(4).Values = "='Shut-Off Cyl Data'!$H$" & FirstLine & ":$H$" & LastLine

End Sub

Private Sub Prepare_Ignition_Data()

Dim StartRow As Long
Dim FinishRow As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    StartRow = Sheets(RawDataSheetName).Range("A1").Value
    FinishRow = Sheets(RawDataSheetName).Range("A2").Value
    
    
    Sheets("Ignition Data").Select
    
    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Range("A4").Formula = "='" & RawDataSheetName & "'!A" & StartRow
    Range("B4").Formula = "='" & RawDataSheetName & "'!U" & StartRow
    Range("C4").Formula = "='" & RawDataSheetName & "'!Y" & StartRow
    Range("D4").Formula = "='" & RawDataSheetName & "'!Z" & StartRow
    Range("E4").Formula = "='" & RawDataSheetName & "'!AC" & StartRow
    Range("F4").Formula = "=B" & StartRow & "/100"
    Range("G4").Formula = "=E" & StartRow
    Range("H4").Formula = "=B" & StartRow & "/1000"
    Range("I4").Formula = "=C" & StartRow
    Range("J4").Formula = "=D" & StartRow
    Range("K4").Formula = "=B" & StartRow & "/10000"
    Range("L4").Formula = "=ABS(I" & StartRow & "-J" & StartRow & ")"
    
    Range("A4:L4").Select
    Selection.AutoFill Destination:=Range("A4:L" & FinishRow), Type:=xlFillDefault
    
    Range("A4").Select
    
End Sub

Private Sub Prepare_Ignition_Angle_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Ignition Angle Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='Ignition Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='Ignition Data'!$F$" & FirstLine & ":$F$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='Ignition Data'!$G$" & FirstLine & ":$G$" & LastLine

End Sub

Private Sub Prepare_Injection_Time_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Injection Time Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='Ignition Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='Ignition Data'!$H$" & FirstLine & ":$H$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='Ignition Data'!$I$" & FirstLine & ":$I$" & LastLine
    ActiveChart.SeriesCollection(3).Values = "='Ignition Data'!$J$" & FirstLine & ":$J$" & LastLine

End Sub

Private Sub Prepare_Injection_Time_Difference_Chart()

Dim FirstLine As Long
Dim LastLine As Long
Dim RawDataSheetName As String


    RawDataSheetName = Sheets(Sheets.Count).Name
    FirstLine = Sheets(RawDataSheetName).Range("A1").Value
    LastLine = Sheets(RawDataSheetName).Range("A2").Value
    
    Sheets("Injection Time Difference Chart").Select
    
    ActiveChart.SeriesCollection(1).XValues = "='Ignition Data'!$A$" & FirstLine & ":$A$" & LastLine
    ActiveChart.SeriesCollection(1).Values = "='Ignition Data'!$K$" & FirstLine & ":$K$" & LastLine
    ActiveChart.SeriesCollection(2).Values = "='Ignition Data'!$L$" & FirstLine & ":$L$" & LastLine

End Sub