Public Mwb As Workbook
Public Mwb2 As Workbook
Public Asheet As Worksheet
Public Bsheet As Worksheet
Public TransWbMaxRow As Variant
Public TransWbMaxCol As Variant
Public TransWbMaxRow2 As Variant
Public TransWbMaxCol2 As Variant

Public Prod_Code(10000) As Variant
Public Prod_Code1(10000) As Variant
Public Prod_Code2(10000) As Variant
Public Prod_Code3(10000) As Variant
Public Prod_Quantity(100000) As Variant
Public Prod_Date(100000) As Variant
Public FU(100000) As Variant
Public PV(100000) As Variant
Public NFA(100000) As Variant
Public RFA(100000) As Variant
Public Prod_year(100000) As Variant
Public months(100000) As Variant
Public weeks(100000) As Variant

Public Prod_Name_Missed(500) As Variant
Public Prod_Code_Missed(500) As Variant
Function GetMaxRow() As Variant
    On Error GoTo met2
    GetMaxRow = ActiveCell.SpecialCells(xlLastCell).Row
    Exit Function
met2:
    Resume Next
End Function
Function GetMaxCol() As Integer
    On Error GoTo met1
    GetMaxCol = ActiveCell.SpecialCells(xlLastCell).Column
    Exit Function
met1:
    Resume Next
End Function
Function fnd()
On Error GoTo err_debug
Exit Function
err_debug:
MsgBox Err.Number & ": " & Err.Description & " on line " & Erl, vbError
End Function

Sub unload_csv()
   Set Mwb = ActiveWorkbook
   Set Asheet = Mwb.ActiveSheet
   
   
   
   countmess% = 0
   Date1 = ""
   aaa% = MsgBox("Áóäåò ñîçäàí csv ôàéë äëÿ çàãðóçêè â SAP.", vbOKCancel + vbInformation, "Âíèìàíèå.")
   If aaa% <> vbOK Then
    End
   End If
   Application.ScreenUpdating = False
   TransWbMaxRow = GetMaxRow()
   

   cnt1# = 0
For Ii# = 8 To TransWbMaxRow

     If Len(Trim(Cells(Ii#, 1).Value)) > 0 Then
        cnt1# = cnt1# + 1
        Prod_Code(cnt1#) = Trim(Cells(Ii#, 9).Value)
        Prod_Code1(cnt1#) = Trim(Cells(Ii#, 6).Value)
        Prod_Code2(cnt1#) = Trim(Cells(Ii#, 7).Value)
        Prod_Code3(cnt1#) = Trim(Cells(Ii#, 8).Value)
        Prod_Quantity(cnt1#) = Trim(Cells(Ii#, 11).Value)
        FU(cnt1#) = Trim(Cells(Ii#, 1).Value)
        PV(cnt1#) = Trim(Cells(Ii#, 2).Value)
        NFA(cnt1#) = Trim(Cells(Ii#, 3).Value)
        RFA(cnt1#) = Trim(Cells(Ii#, 4).Value)

    End If
   Next Ii#
        
     
      
      Application.ScreenUpdating = False
   TransWbMaxCol = GetMaxCol()
      cnt2# = 0
   For jj# = 11 To TransWbMaxCol
    If Len(Trim(Cells(jj#, 1).Value)) > 0 Then
        cnt2# = cnt2# + 1
        months(cnt2#) = Trim(Cells(7, jj#).Value)
        weeks(cnt2#) = Trim(Cells(1, jj#).Value)
    End If
   Next jj#
        
        
        
    Jk = FreeFile()
    Open "C:\APO\0001.csv" For Output As Jk
    Print #Jk, "APO - Plng Version" & ";" & "Sales Org" & ";" & "Product" & ";" & "Forecast Unit" & ";" & "Product Variant" & ";"; "National Forecast Ac" & ";" & "Regional Forecast Ac" & ";" & "Customer country" & ";" & "APO - Location" & ";" & "Distribution Channel" & ";" & "Calendar week" & ";" & "Calendar Months" & ";" & "Unit of measure" & ";" & "Budget" & ";" & "Reestimate" & ";" & "Historical Mark events" & ";" & "Historical Other events" & ";" & "Historical sales events" & ";" & "Base forecast"; ";" & "Base forecast correction" & ";" & "Base forecast distribution" & ";" & "Marketing events" & ";" & "Sales events" & ";" & "Other events" & ";" & "Future events4" & ";" & "Additional events" & ";" & "Artkey" & ";" & "Timedis" & ";" & "PWF"
    Ln# = 2
    For jj1# = 1 To cnt2#
    For Ii1# = 1 To cnt1#
    If NFA(Ii1#) = "089030" Then
       If Prod_Code1(Ii1#) = 0 Then
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
       Else
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0"
       Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0"
       End If
    Else
   
       If Prod_Code1(Ii1#) = 0 And Prod_Code2(Ii1#) <> "empty" And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
       Else
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code1(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & Round(Prod_Quantity(Ii1#), 0) & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        If Prod_Code2(Ii1#) > 0 And Prod_Code2(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code2(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
        If Prod_Code3(Ii1#) > 0 And Prod_Code3(Ii1#) <> "empty" Then
        Print #Jk, "001" & ";" & "5200" & ";" & Prod_Code3(Ii1#) & ";" & FU(Ii1#) & ";" & PV(Ii1#) & ";" & NFA(Ii1#) & ";" & RFA(Ii1#) & ";" & "" & ";" & "5201" & ";" & "06" & ";" & weeks(jj1#) & ";" & months(jj1#) & ";" & "PC" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"; ";" & "1" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0" & ";" & "0"
        End If
       End If
    End If
     Next Ii1#
     Next jj1#
    
           ' Print #jj%, Val(Cells(i%, 3).Value) & ",", Mid(Sums$, 1, Len(Sums$) - 1)
    Close #Jk

    Application.ScreenUpdating = True
End Sub