Sub BANK01_Hernoemen() 'BANK downloadbestand inlezen, in cel laden en declareren
Application.ScreenUpdating = False
Dim sFil As String
Dim sPath As String
Dim lRij As Long
Dim TempName As String
With Sheets("Variabelen")
lRij = 1
sFil = Dir(.Range("D15") & "\" & .Range("E15") & "*" & .Range("F15")) 'Bestandsdefinitie en filter
Do While sFil <> ""
.Range("A" & lRij) = sFil
lRij = lRij + 1
sFil = Dir
Loop
End With
'Benoemen van tijdelijke bestandsnaam voor importeren
With Sheets("Variabelen")
TempName = .Range("H15")
If Len(TempName) > 0 Then
TempName = .Range("H15")
Else
MsgBox "Bestand is niet gevonden", vbInformation, "Onvindbaar"
'Macro stopt bij "Bestand is niet gevonden" / Start de volgende sub niet.
GoTo Fout1
End If
End With
'Mutatiebestand Importen
ActiveWorkbook.Worksheets("ImportBANK").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TempName, Destination:=Range("$A$8"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 4, 1, 1, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Call BANK03_Kolomschikking 'roept volgende macro aan
Fout1:
End Sub
Sub BANK03_Kolomschikking() 'BANK_Kolomschikking
Application.ScreenUpdating = False
Rows("8:9").Select
Selection.Delete Shift:=xlUp
Range("B8:B" & [A65536].End(xlUp).Row).Select
Application.CutCopyMode = False
Range("F8:G" & [A65536].End(xlUp).Row).Select
Selection.Cut
Range("B8:B" & [A65536].End(xlUp).Row).Select
Selection.Insert Shift:=xlToRight
Range("H8:H" & [A65536].End(xlUp).Row).Select
Selection.Cut
Range("E8:E" & [A65536].End(xlUp).Row).Select
Selection.Insert Shift:=xlToRight
Range("I8:S" & [A65536].End(xlUp).Row).Select 'aantal kolommen invoegen voor het aantal mutaties.
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Call BANK04_Kolomeigenschappen 'roept volgende macro aan
End Sub
Sub BANK04_Kolomeigenschappen() 'BANK04_Kolomeigenschappen
Application.ScreenUpdating = False
Selection.Delete Shift:=xlUp
Columns("B:B").Select
Selection.NumberFormat = "0"
Columns("J:P").Select
Selection.NumberFormat = "0.00"
Columns("H:I").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Columns("A:A").ColumnWidth = 10
Columns("B:B").ColumnWidth = 20
Columns("C:C").ColumnWidth = 13.14
Columns("D:E").ColumnWidth = 8
Columns("F:G").ColumnWidth = 5
Columns("H:Q").ColumnWidth = 13.14
Columns("R:T").ColumnWidth = 4.86
Columns("U:U").ColumnWidth = 16
Columns("V:V").ColumnWidth = 52
Columns("W:Z").ColumnWidth = 10
Columns("AA:AE").ColumnWidth = 14
Columns("AF:BD").ColumnWidth = 8.43
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlRight
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("B:G").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("D:E").Select
Selection.NumberFormat = "dd-mm-yy"
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Rows("3:3").Select
Selection.NumberFormat = "0"
Call BANK05_Formules_kopiëren
End Sub
Sub BANK05_Formules_kopiëren() 'Formules_kopiëren
Application.ScreenUpdating = False
Range("I5:S5").Copy Destination:=Range("I8:S" & [A65536].End(xlUp).Row) 'formules in kolommen kopiëren
Range("AD5:BE5").Copy Destination:=Range("AD8:BE" & [A65536].End(xlUp).Row) 'formules in kolommen kopiëren
'Importdata Opmaken
Range("A8:BD" & [A65536].End(xlUp).Row).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline
End With
Range("U5:AF5").Select
Selection.Copy
Range("U8:AF" & [A65536].End(xlUp).Row).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Call BANK06_filter
End Sub
Sub BANK06_filter() 'mutaties voor ingestelde datum (variabelen $H$3) verwijderen
Application.ScreenUpdating = False
With Sheets("importBANK").Cells(7, 1).CurrentRegion
.AutoFilter 4, "<" & Format(Sheets("Variabelen").Cells(3, 8), "m-d-yyyy")
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Call BANK07_Verplaatsen
End Sub
Sub BANK07_Verplaatsen() 'Overgebleven rijen verplaatsen naar het overzichtswerkblad
Application.ScreenUpdating = False
Dim lLaatsteRij As Long
lLaatsteRij = Sheets("ImportBANK").Range("A65536").End(xlUp).Row 'als A8 op blad Import leeg is alles overslaan
If lLaatsteRij >= 8 Then
Sheets("ImportBANK").Rows("8:" & lLaatsteRij).Cut
lLaatsteRij = Sheets("Bank").Range("A65536").End(xlUp).Row + 1 'als A8 op blad Bank leeg is, moet lLaatsteRij = 8 worden
lLaatsteRij = -(lLaatsteRij < 6) * 6 - lLaatsteRij * (lLaatsteRij >= 6)
Sheets("Bank").Range("A" & lLaatsteRij).EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End If
ActiveWorkbook.Worksheets("Bank").Select
lLaatsteRij = Sheets("Bank").Range("A65536").End(xlUp).Row + 1
Sheets("Bank").Select
Sheets("Bank").Range("A" & lLaatsteRij).Select
ActiveWindow.ScrollRow = lLaatsteRij - (Range("A2") + 1)
'Importdata Opmaken
Range("A6:B" & lLaatsteRij).Select
With Selection.Font
.Name = "Courier New"
.FontStyle = "Vet"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Call BANK08_Dubbelingen_VB
End Sub
Sub BANK08_Dubbelingen_VB() 'Dubbelingen markeren
Application.ScreenUpdating = False
Dim lLaatsteRij As Long
lLaatsteRij = Range("A5").End(xlDown).Row
Rows("6:" & lLaatsteRij).Select 'begin van werkrij
Range("6:" & lLaatsteRij).Activate
ActiveWorkbook.Worksheets("Bank").Select
Range("BC4").Copy Destination:=Range("BC6:BC" & [A65536].End(xlUp).Row)
Call Naar_Laatste_Regel
End Sub
Sub BANK08_Dubbelingen() 'Dubbelingen verwijderen
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Bank").Select
With Sheets("Bank")
For i = .UsedRange.Rows.Count To 1 Step -1
If IsNumeric(Left(.Cells(i, 55), 55)) Then 'de 56 staat voor de 67e kolom
If (.Cells(i, 55).Value) = 2 Then .Cells(i, 55).EntireRow.Delete
End If
Next
End With
Call BANK_Opruimen
Call BANK09_Sorteren
End Sub
Sub BANK09_Sorteren() 'Aantal rijen controleren om te kunnen sorteren
Application.ScreenUpdating = False
With Sheets("Bank")
If Range("A2") > 2 Then
Call BANK09_Sorteren_2
Else
MsgBox "Te weinig mutaties om te sorteren", vbInformation, "Geen sortering"
'Macro stopt bij "Te weinig mutaties om te sorteren" / Gaat naar het einde.
Call Einde
End If
End With
End Sub
Sub BANK09_Sorteren_2() 'Rijen sorteren
Application.ScreenUpdating = False
Dim lLaatsteRij As Long
lLaatsteRij = Range("A5").End(xlDown).Row
Rows("7:" & lLaatsteRij).Select
Range("U5:U" & lLaatsteRij).Select
Selection.NumberFormat = "0"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWorkbook.Worksheets("Bank").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bank").Sort.SortFields.Add Key:=Range("D7:D" & lLaatsteRij), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Valutadatum
ActiveWorkbook.Worksheets("Bank").Sort.SortFields.Add Key:=Range("E7:E" & lLaatsteRij), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Transactiedatum
ActiveWorkbook.Worksheets("Bank").Sort.SortFields.Add Key:=Range("B7:B" & lLaatsteRij), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal 'Tegenrekeningnummer
With ActiveWorkbook.Worksheets("Bank").Sort
.SetRange Range("A7:BE" & lLaatsteRij) 'de 1e saldo-regel(A6) wordt niet meegenomen
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Call Naar_Laatste_Regel
End Sub
Sub BANK_AFRONDEN() 'Cellen kleuren, formules verwijderen, opmaak aanpassen
Application.ScreenUpdating = False
Dim lLaatsteRij As Long
lLaatsteRij = Range("A5").End(xlDown).Row
Rows("7:" & lLaatsteRij).Select
'kleurt de kolom budgetcodes
Range("S6:T" & lLaatsteRij).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543 'kleur licht geel
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("BB7:BC" & lLaatsteRij - 5).Select
With Selection.Interior
.Color = 10092543 'kleur licht geel
End With
'Formules verwijderen in kolommmen van werkgebied A6:T
With Sheets("Bank")
If Range("A2") < 10 Then
MsgBox "Te weinig regels om formules te verwijderen", vbInformation, "Geen formules verwijderen"
'Macro stopt bij "Te weinig regels" / Gaat naar het einde.
Call Einde
Else
Call BANK09_Sorteren_2
Range("A7:J" & lLaatsteRij - 5) = Range("A7:J" & lLaatsteRij - 5).Value 'gebruikt geen klembord
Range("L7:T" & lLaatsteRij - 5) = Range("L7:T" & lLaatsteRij - 5).Value 'gebruikt geen klembord
Range("Z7:Z" & lLaatsteRij) = Range("Z7:Z" & lLaatsteRij).Value 'gebruikt geen klembord
Range("I5:I" & lLaatsteRij).Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("BB7:BC" & lLaatsteRij - 5).Select
With Selection.Interior
.Color = 10092543 'kleur licht geel
End With
ActiveWindow.SmallScroll ToRight:=-80
End If
End With
Call Naar_Laatste_Regel
Application.ScreenUpdating = True
End Sub
Sub BANK_Opruimen() 'Tijdelijk bestand verwijderen
Application.ScreenUpdating = False
Dim TempName As String
With Sheets("Variabelen")
TempName = .Range("H15")
If Len(TempName) > 0 Then
' TempName = .Range("H15")
Else
' MsgBox "Bestand was reeds verwijderd", vbInformation, "Onvindbaar"
'Macro stopt bij "Bestand is niet gevonden"
GoTo Fout2
End If
End With
'Importbestand wissen
Kill TempName
Fout2:
'Bestandsnaam wissen in Cel A1 - Variabelen
Sheets("Variabelen").Select
Range("A1").Select
Selection.ClearContents
Sheets("Bank").Select
Call Naar_Laatste_Regel
Application.ScreenUpdating = True
End Sub
Sub Naar_Laatste_Regel() 'Naar 1e lege regel
Application.ScreenUpdating = False 'Voorkomt flikkeren van het beeldscherm
ActiveWindow.ScrollColumn = 1
Dim lLaatsteRij As Long
lLaatsteRij = Range("A5").End(xlDown).Row
Rows("6:" & lLaatsteRij).Select
ActiveWorkbook.Worksheets("Bank").Select
Range("A" & [A65536].End(xlUp).Row + 1).Select 'cursor naar 1e lege cel van sheet Bank
Selection.ClearContents
ActiveWindow.ScrollRow = lLaatsteRij - Range("A2") 'flexibele LaatsteRij - aantal in cel A2
Application.ScreenUpdating = True
End Sub
Sub Einde()
Range("A4").Select
Application.ScreenUpdating = True
End Sub