Sub nieuw()
Dim datum As String
datum = InputBox("Welke dag wil je afdrukken?" & vbCrLf & "Geef datum in dag/maand", "Dagplanning", "Datum")
If Not IsDate(datum) Then
MsgBox "Datum is niet correct, geef in als 23/03 of 23/03/1984 !", vbCritical, "Fout!"
Else
With Sheets("historiek")
.Range("A2").Value = datum
.Range("A2").NumberFormat = "dd\/mm\/yyyy"
End With
End If
End Sub
Sub Cow18_2()
Dim rw, shCopy, shPaste, datum
Set shCopy = ActiveSheet
With ActiveSheet
datum = IIf(IsDate(.Range("A4")), CLng(.Range("A4")), CLng(Date)) 'staat er een datum in cel A4, dan neem je die datum
rw = Application.Match(datum, .Rows(14), 0) 'zoek datum in huidig werkblad
If IsError(rw) Then 'niet gevonden
For Each Sh In ThisWorkbook.Worksheets 'alle werkbladen afzoeken
rw = Application.Match(datum, Sh.Rows(14), 0) 'anders neem je vandaag
If Not IsError(rw) Then Set shCopy = Sh: Exit For 'gevonden in een bepaald blad, dan is het die en uit de loop treden
Next
End If
End With
If Not IsError(rw) Then
With shCopy
.Columns("F:ad").Hidden = True
.Range("14:15,36:37,60:61").EntireRow.Hidden = True
.Columns(rw).Resize(, 4).Hidden = False
Set shPaste = Sheets.Add
shCopy.Range("A16:D75").SpecialCells(xlCellTypeVisible).Copy
shPaste.Range("A1").PasteSpecial Paste:=xlPasteAll 'probleempje met samengevoegde cellen !!!
shCopy.Range("F16:Ac75").SpecialCells(xlCellTypeVisible).Copy
shPaste.Range("E1").PasteSpecial Paste:=xlPasteAll
With shPaste
.UsedRange.Offset(, 3).SpecialCells(4).Interior.Color = xlNone
With .PageSetup
.CenterHeader = Format(datum, "long date")
.CenterVertically = True
.CenterHorizontally = True
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.FitToPagesTall = 1
.FitToPagesWide = 1
.Zoom = False
.HeaderMargin = 10
.TopMargin = 25
.BottomMargin = 10
End With
.PrintPreview
End With
Application.DisplayAlerts = False
shPaste.Delete
Application.DisplayAlerts = True
.UsedRange.EntireRow.Hidden = False
.UsedRange.EntireColumn.Hidden = False
Application.CutCopyMode = False
End With
End If
End Sub