twin | Text mode window environment | Command Line Interface library

Sub ExportCreatePOD()

'File names to be created as " & " " & "-" & " " & "POL" & " " & "-" & " " & Format(Now()," MM-DD-YYYY ")"


    Dim RngSourceData As Range
    Dim RngTarget As Range
    Dim RngRange01 As Range
    Dim RngRange02 As Range
    Dim StrCarrierColumnHeader As String
    Dim StrSavePath As String
    Dim StrMultipleFileMessage As String
    Dim DblCarrierColumnRelativeColumn As Double
    Dim DblCounter01 As Double
    Dim DblCounter02 As Double
    Dim WkbSource As Workbook
    Dim WkbTarget As Workbook
    
    'Turning off screen updating.
    Application.ScreenUpdating = False
    
    'Setting variables.
    Set WkbSource = ActiveWorkbook
    Set RngSourceData = WkbSource.Sheets("POD").UsedRange
    StrCarrierColumnHeader = "Carrier"
    StrSavePath = "C:Path\" '=== Path for POD files Change the Path (where you want to export the POD files)
    
  
    On Error Resume Next
    DblCarrierColumnRelativeColumn = Excel.WorksheetFunction.Match(StrCarrierColumnHeader, RngSourceData.Rows(1), 0)
    If Err <> 0 Then
        MsgBox "The range " & RngSourceData.Rows(1).Address(False, False) & " contains no column headed " & StrCarrierColumnHeader & ". The subroutine is terminated", vbCritical, "Error"
        Application.ScreenUpdating = True
        Exit Sub
    End If
    On Error GoTo 0
    
  
    Set RngRange01 = RngSourceData.Columns(DblCarrierColumnRelativeColumn).Resize(RngSourceData.Rows.Count - 1).Offset(1, 0)
    

    DblCounter01 = 0
    For Each RngTarget In RngRange01.Cells
        

        If Excel.WorksheetFunction.CountIf(RngSourceData.Parent.Range(RngRange01.Cells(1, 1), RngTarget), RngTarget.Value) = 1 Then
            
            'Cheking if any file dedicated to the given carrier already exists for today.
            If Dir(StrSavePath & RngTarget.Value & " " & "-" & " " & "POD" & " " & "-" & " " & Format(Now(), " MM-DD-YYYY ") & ".xlsx") = "" Then
                'If no such file exists, it is created and saved.
                Set WkbTarget = Workbooks.Add
                WkbTarget.SaveAs StrSavePath & RngTarget.Value & " " & "-" & " " & "POD" & " " & "-" & " " & Format(Now(), " MM-DD-YYYY ") & ".xlsx"
            Else
                'Is it does exist, the name is "shifted".
                DblCounter02 = 2
                Do Until Dir(StrSavePath & RngTarget.Value & " " & "-" & " " & "POD" & " " & "-" & " " & Format(Now(), " MM-DD-YYYY ") & "(" & DblCounter02 & ")" & ".xlsx") = ""
                    DblCounter02 = DblCounter02 + 1
                Loop
                'Carrier and relative file are copied in StrMultipleFileMessage.
                StrMultipleFileMessage = StrMultipleFileMessage & vbCrLf & RngTarget.Value & " in " & RngTarget.Value & " " & "-" & " " & "POD" & " " & "-" & " " & Format(Now(), " MM-DD-YYYY ") & "(" & DblCounter02 & ")" & ".xlsx"
                Set WkbTarget = Workbooks.Add
                WkbTarget.SaveAs StrSavePath & RngTarget.Value & " " & "-" & " " & "POD" & " " & "-" & " " & Format(Now(), " MM-DD-YYYY ") & "(" & DblCounter02 & ")" & ".xlsx"
            End If
            
            'Setting RngRange02 to target the range in the new file where RngSourceData will be copied.
            Set RngRange02 = WkbTarget.Sheets(1).Range("A1").Resize(RngSourceData.Rows.Count, RngSourceData.Columns.Count)
            
            With RngRange02
                
                'Copying values.
                RngSourceData.Copy RngRange02
                
                'Filtering the range to clear the list of unwanted data.
                .AutoFilter Field:=DblCarrierColumnRelativeColumn, Criteria1:="<>" & RngTarget.Value
                .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).ClearContents
                .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).ClearFormats
                
                'Removing the filter.
                .AutoFilter
                
                'Rename Sheet
                 Sheets("Sheet1").Name = "POD"
                 
                 'Autofit
                
                Sheets("POD").UsedRange.Columns.AutoFit
                
          
               
                
                'Sorting the range to compact the data.
                With .Parent.Sort
                     .SortFields.Clear
                     .SortFields.Add Key:=RngRange02.Columns(DblCarrierColumnRelativeColumn), _
                                    SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, _
                                    DataOption:=xlSortNormal
                    .SetRange RngRange02
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                
      
                
            End With
            
            'Saving and closing WkbTarget.
            WkbTarget.Close SaveChanges:=True
            
        End If
        
        'Setting DblCounter01.
        DblCounter01 = DblCounter01 + 1
    Next
    
    'Enabling screen updating.
    Application.ScreenUpdating = True
    
    'Reporting if any carrier had its data reported in a "twin" file.
    If StrMultipleFileMessage <> "" Then
        StrMultipleFileMessage = "The following carriers had already one or more dedicated files at the given path. Their data were saved accordingly to this list:" & vbCrLf & vbCrLf & StrMultipleFileMessage
        MsgBox StrMultipleFileMessage, , "Multiple dedicated files"
    End If

End Sub