Sub DeleteRowsUntilFirstNonNA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    
    ' Find the last row in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each cell in column B starting from row 2
    For i = 3 To lastRow
        ' Check if the cell value is not #N/A
        If Not IsError(ws.Cells(i, "B").Value) Then
            ' Select and delete the range from row 2 to the first non-NA row
            ws.Rows("2:" & i).Delete
            Exit Sub ' Exit the loop once the rows are deleted
        End If
    Next i
    
    ' If no non-NA value is found, display a message
    MsgBox "No non-NA values found in column B."
End Sub

Sub ConvertDates()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dateRange As Range
    Dim convertedRange As Range
    Dim dateValues As Variant
    Dim convertedValues As Variant
    Dim i As Long
    
    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    
    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Set the range of dates in column A
    Set dateRange = ws.Range("A2:A" & lastRow) ' Assuming data starts from row 2
    
    ' Insert a new column after column A
    ws.Columns("B:B").Insert Shift:=xlToRight
    ws.Range("B1").Value = "date"

    
    ' Set the range for the converted dates in the newly inserted column
    Set convertedRange = ws.Range("B2:B" & lastRow)
    
    ' Read the date values into an array for faster processing
    dateValues = dateRange.Value
    
    ' Resize the converted values array to match the size of the date values array
    ReDim convertedValues(1 To UBound(dateValues, 1), 1 To UBound(dateValues, 2))
    
    ' Loop through each date in the array and convert the format
    For i = 1 To UBound(dateValues, 1)
        convertedValues(i, 1) = Format(dateValues(i, 1), "YYYYMMDD")
    Next i
    
    ' Write the converted values back to the worksheet
    convertedRange.Value = convertedValues
    convertedRange.NumberFormat = "General"

    ' Copy the value from A1 to C1
    ws.Range("C1").Value = ws.Range("A1").Value
    
    ' Write "Dates" in A1
    ws.Range("A1").Value = "Dates"
    
    ' Autofit the columns to adjust the width
    ws.Columns("A:B").AutoFit
End Sub

Sub SaveWorkbookAsFilename()
    Dim ws As Worksheet
    Dim filePath As String
    Dim fileName As String
    
    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    
    filePath = "C:\Users\fubbe\Desktop\All_Spot_Final\"
    fileName = ws.Range("C1").Value & ".xlsm"
    
    ' Save the workbook with the specified file name and path
    ThisWorkbook.SaveAs filePath & fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    ' Display a message with the saved file path
    'MsgBox "Workbook saved as: " & filePath & fileName
End Sub

Sub DeleteColumns()
    Dim ws As Worksheet
    
    ' Set the active worksheet
    Set ws = ActiveSheet
    
    ' Delete columns A to C
    ws.Range("A:C").Delete Shift:=xlToLeft
End Sub

Sub FormatSpotData()
    DeleteRowsUntilFirstNonNA
    ConvertDates
    SaveWorkbookAsFilename
    DeleteColumns
    
End Sub