Attribute VB_Name = "Module1"
'C:\Users\marti\Disk Google\Dům\Soubory\Účetnictví\2019\makro SQL verze 2.1.bas

Public PrvníŘádka
Public PosledníŘádka
Public Transakce

Sub doplnit()


Sešit = ThisWorkbook.Sheets("Parametry").Cells(6, 2).Value
SouborUctu = ThisWorkbook.Path & "\" & Sešit
Workbooks.Open SouborUctu
'MsgBox Workbooks(Sešit).Sheets("Ucty").Cells(1, 1)


Transakce = "Transakce"
ThisWorkbook.Sheets(Transakce).Activate
ThisWorkbook.Sheets(Transakce).Select
For i = 7 To 65000
If ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value = "" Then Exit For

Next i
PosledníŘádka = i - 1
ActiveWorkbook.Worksheets(Transakce).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(Transakce).Sort.SortFields.Add Key:=ActiveWorkbook.Worksheets(Transakce).Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
ActiveWorkbook.Worksheets(Transakce).Sort.SortFields.Add Key:=ActiveWorkbook.Worksheets(Transakce).Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
ActiveWorkbook.Worksheets(Transakce).Sort.SortFields.Add Key:=ActiveWorkbook.Worksheets(Transakce).Columns(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(Transakce).Sort
        .SetRange ActiveWorkbook.Worksheets(Transakce).Range(Cells(7, 1), Cells(PosledníŘádka, 255))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
PrvníŘádka = 7





For i = PrvníŘádka To 65000
If ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value = "" Then Exit For
If ThisWorkbook.Sheets("Parametry").Cells(1, 1).Value = Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) Then
PrvníŘádka = i
Exit For
End If
Next i




For i = PrvníŘádka To 65000
    If ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value = "" Then GoTo Konec:
    If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "" Then
    '              hledám dozadu
        If i < 8 Then GoTo skočík_a:
        
        Text1 = ThisWorkbook.Sheets(Transakce).Cells(i, 4).Value
        Text2 = ThisWorkbook.Sheets(Transakce).Cells(i, 5).Value
        Text3 = ThisWorkbook.Sheets(Transakce).Cells(i, 6).Value
        
        For j = i - 1 To 7 Step -1
       
        
        
        
        
        
        
        Text5 = ThisWorkbook.Sheets(Transakce).Cells(j, 4).Value
            If ThisWorkbook.Sheets(Transakce).Cells(j, 4).Value = "NájemZáloha" Then
            Text5 = "záloha"
            End If
            
         
            If Text1 = Text5 And Text2 = ThisWorkbook.Sheets(Transakce).Cells(j, 5).Value And Text3 = ThisWorkbook.Sheets(Transakce).Cells(j, 6).Value Then
                If ThisWorkbook.Sheets(Transakce).Cells(j, 8).Value <> "" Then
                    ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = ThisWorkbook.Sheets(Transakce).Cells(j, 9).Value + 1
                    ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = ThisWorkbook.Sheets(Transakce).Cells(j, 7).Value
                 
                    GoTo skočík_a:
                End If
            End If
        Next j
    End If



skočík_a:

If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value <> "" Then GoTo skočík_b:
For m = 1 To 60000
    If ThisWorkbook.Sheets("OdCoKomuNový").Cells(m, 1).Value = "" Then
        ThisWorkbook.Sheets(Transakce).Cells(i, 4).Interior.ColorIndex = 40
        GoTo skočík_b:
    Else
        If ThisWorkbook.Sheets("OdCoKomuNový").Cells(m, 1).Value = ThisWorkbook.Sheets(Transakce).Cells(i, 4).Value Then
        ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = ThisWorkbook.Sheets("OdCoKomuNový").Cells(m, 3).Value
        ThisWorkbook.Sheets("OdCoKomuNový").Cells(m, 4).Value = ThisWorkbook.Sheets("OdCoKomuNový").Cells(m, 4).Value + 1
        GoTo skočík_b:
        End If
    End If
Next m
MsgBox "chyba"





skočík_b:

If ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value <> "" Then GoTo skočík_c:
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "D" Then
    ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value
    ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "M" Then
    If ThisWorkbook.Sheets(Transakce).Cells(i, 5).Value = "Zelinková" Or ThisWorkbook.Sheets(Transakce).Cells(i, 5).Value = "Koucký" Or ThisWorkbook.Sheets(Transakce).Cells(i, 5).Value = "Internet" Or ThisWorkbook.Sheets(Transakce).Cells(i, 5).Value = "Úklid" Then
        ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), Month(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), 1))
        ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), Month(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) + 1, 1) - 1)
        Stop
    Else
        ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), Month(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) + 1, 1))
        ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), Month(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) + 2, 1) - 1)
    End If
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "R-" Then
    ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) - 1, 1, 1))
    ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) - 1, 13, 1) - 1)
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "R" Then
    ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), 1, 1))
    ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), 13, 1) - 1)
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "R+" Then
    ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) + 1, 1, 1))
    ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) + 1, 13, 1) - 1)
End If


skočík_c:

If ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value <> "" Then GoTo skočík_d:
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "D" Then
ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value
ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "M" Then
ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value), Month(ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value) + 1, 1) - 1)
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "R-" Then
ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) - 1, 1, 1))
ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) - 1, 13, 1) - 1)
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "R" Then
ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), 1, 1))
ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value), 13, 1) - 1)
End If
If ThisWorkbook.Sheets(Transakce).Cells(i, 7).Value = "R+" Then
ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) + 1, 1, 1))
ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value = DateValue(DateSerial(Year(ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value) + 1, 13, 1) - 1)
End If
                   
skočík_d:

Next i
                    
                    
Stop
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
Konec:
                    
For i = PrvníŘádka To 65000
                    If ThisWorkbook.Sheets(Transakce).Cells(i, 1).Value = "" Then GoTo koneckonec:
                    If ThisWorkbook.Sheets(Transakce).Cells(i, 4).Value = "NájemZáloha" Then
                    For j = 2 To 64000
                        
                       
                        If Workbooks(Sešit).Sheets("Kontrakt").Cells(j, 1).Value = "" Then
                              ThisWorkbook.Sheets(Transakce).Cells(i, 5).Interior.ColorIndex = 20
                              GoTo Dalsiii:
                        End If
                        If ThisWorkbook.Sheets(Transakce).Cells(i, 5).Value = Workbooks(Sešit).Sheets("Kontrakt").Cells(j, 1).Value And ThisWorkbook.Sheets(Transakce).Cells(i, 8).Value >= Workbooks(Sešit).Sheets("Kontrakt").Cells(j, 8).Value And ThisWorkbook.Sheets(Transakce).Cells(i, 9).Value <= Workbooks(Sešit).Sheets("Kontrakt").Cells(j, 9).Value Then
                                        PomNájem = Workbooks(Sešit).Sheets("Kontrakt").Cells(j, 16).Value
                                        PomZáloha = Workbooks(Sešit).Sheets("Kontrakt").Cells(j, 17).Value
                                        PomCelkem = ThisWorkbook.Sheets(Transakce).Cells(i, 11).Value
                                        ThisWorkbook.Sheets(Transakce).Rows(i + 1).Insert
                                        ThisWorkbook.Sheets(Transakce).Rows(i).Select
                                        Selection.Copy
                                        ThisWorkbook.Sheets(Transakce).Rows(i + 1).Select
                                        ThisWorkbook.Sheets(Transakce).Paste
                                        ThisWorkbook.Sheets(Transakce).Cells(i, 4).Value = "nájem"
                                        ThisWorkbook.Sheets(Transakce).Cells(i + 1, 4).Value = "záloha"
                                        ThisWorkbook.Sheets(Transakce).Cells(i, 11).Value = PomNájem
                                        ThisWorkbook.Sheets(Transakce).Cells(i + 1, 11).Value = PomCelkem - PomNájem
                                        If PomCelkem <> PomNájem + PomZáloha Then
                                           ThisWorkbook.Sheets(Transakce).Cells(i + 1, 11).Interior.ColorIndex = 40
                                        End If
                                        GoTo Dalsiii:
                    
                        End If
                            
                    Next j
                    
Dalsiii:
                    
                    End If
                    


Next i



koneckonec:

PosledníŘádka = i - 1

ThisWorkbook.Sheets(Transakce).Cells.Borders(xlDiagonalDown).LineStyle = xlNone
ThisWorkbook.Sheets(Transakce).Cells.Borders(xlDiagonalUp).LineStyle = xlNone
ThisWorkbook.Sheets(Transakce).Cells.Borders(xlEdgeLeft).LineStyle = xlNone
ThisWorkbook.Sheets(Transakce).Cells.Borders(xlEdgeTop).LineStyle = xlNone
ThisWorkbook.Sheets(Transakce).Cells.Borders(xlEdgeBottom).LineStyle = xlNone
ThisWorkbook.Sheets(Transakce).Cells.Borders(xlEdgeRight).LineStyle = xlNone
ThisWorkbook.Sheets(Transakce).Cells.Borders(xlInsideVertical).LineStyle = xlNone
ThisWorkbook.Sheets(Transakce).Cells.Borders(xlInsideHorizontal).LineStyle = xlNone

Application.CutCopyMode = False

ThisWorkbook.Sheets("Kopie").Select
ThisWorkbook.Sheets("Kopie").Cells(1, 1).Activate
ThisWorkbook.Sheets("Kopie").Range(Cells(1, 1), Cells(6, 250)).Copy

ThisWorkbook.Sheets(Transakce).Select
ThisWorkbook.Sheets(Transakce).Cells(1, 1).Activate
ThisWorkbook.Sheets(Transakce).Range(Cells(1, 1), Cells(6, 250)).Select
ThisWorkbook.Sheets(Transakce).Paste

'Special Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False



ThisWorkbook.Sheets("Kopie").Select
ThisWorkbook.Sheets("Kopie").Cells(1, 1).Activate
ThisWorkbook.Sheets("Kopie").Range(Cells(7, 13), Cells(7, 250)).Copy
ThisWorkbook.Sheets(Transakce).Select
ThisWorkbook.Sheets(Transakce).Cells(1, 1).Activate
ThisWorkbook.Sheets(Transakce).Range(Cells(PrvníŘádka, 13), Cells(PosledníŘádka, 250)).Select
ThisWorkbook.Sheets(Transakce).Paste


'Special Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ThisWorkbook.Sheets("Kopie").Select
ThisWorkbook.Sheets("Kopie").Cells(1, 1).Activate
ThisWorkbook.Sheets("Kopie").Range(Cells(7, 1), Cells(7, 250)).Copy

ThisWorkbook.Sheets(Transakce).Select
ThisWorkbook.Sheets(Transakce).Cells(1, 1).Activate
ThisWorkbook.Sheets(Transakce).Range(Cells(7, 1), Cells(PosledníŘádka, 250)).Select

ThisWorkbook.Sheets(Transakce).Range(Cells(7, 1), Cells(PosledníŘádka, 250)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 ThisWorkbook.Sheets(Transakce).Cells.Columns.AutoFit
 ThisWorkbook.Sheets(Transakce).Cells.Rows.AutoFit
 ThisWorkbook.Sheets(Transakce).Cells.Columns.AutoFit
 ThisWorkbook.Sheets(Transakce).Cells.Rows.AutoFit
ThisWorkbook.Sheets(Transakce).Range(Cells(2, 16), Cells(60000, 16)).Columns.AutoFit
ThisWorkbook.Sheets(Transakce).Rows(5).Rows.AutoFit



ThisWorkbook.Sheets(Transakce).Rows("6:6").RowHeight = 0
 For i = 1 To 200
 On Error Resume Next
 ThisWorkbook.Sheets(Transakce).Columns(i).ColumnWidth = ThisWorkbook.Sheets(Transakce).Columns(i).ColumnWidth + 0.4
  

 
 
 
 On Error GoTo 0
 Next i
 ThisWorkbook.Sheets(Transakce).Rows(5).RowHeight = ThisWorkbook.Sheets(Transakce).Rows(5).RowHeight + 1
 
 

 
 
 
 ThisWorkbook.Sheets(Transakce).Range(Cells(PosledníŘádka, 1), Cells(PosledníŘádka, 26)).Borders(xlEdgeBottom).Weight = xlThick

Workbooks(Sešit).Save
Workbooks(Sešit).Close
ThisWorkbook.Sheets(Transakce).Cells(1, 1).Select
Application.DisplayStatusBar = True
Application.StatusBar = "Text*********************"
Set Wshshell = CreateObject("WScript.Shell")
Wshshell.Popup "Vygenerováno ", 2, ""
ThisWorkbook.Save
End Sub
Sub TiskAll()
Application.Run "doplnit"
Application.Run "TiskMakro", 5, 6, 3, 12, 1, 2
Application.Run "TiskMakro", 1, 6, 18, 26, 16, 17
End Sub

Sub TiskMakro(Odnadpis, Donadpis, Odsloupec, Dosloupec, Odvlevo, Dovlevo)
    
    Application.PrintCommunication = False
    With ThisWorkbook.Sheets(Transakce).PageSetup
        .PrintTitleRows = Range(Cells(Odnadpis, Odsloupec), Cells(Donadpis, Dosloupec)).Address
        .PrintTitleColumns = Range(Cells(Odnadpis, Odvlevo), Cells(Donadpis, Dovlevo)).Address
    End With
    Application.PrintCommunication = True
    ThisWorkbook.Sheets(Transakce).PageSetup.PrintArea = Range(Cells(PrvníŘádka, Odsloupec), Cells(PosledníŘádka, Dosloupec)).Address
    Application.PrintCommunication = False
        With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "&P/&N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.3)
        .RightMargin = Application.InchesToPoints(0.3)
        .TopMargin = Application.InchesToPoints(0.9)
        .BottomMargin = Application.InchesToPoints(0.3)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    
 
    ActiveWindow.ActiveSheet.PrintOut Preview:=True
 
End Sub

