PDA

View Full Version : VBA problem can anybody help



eddycordo
01-16-2016, 04:41 PM
Sub VolgFact()
Range("Uren!R3").Value = Range("Uren!R3").Value + 1
Range("Voorblad!I40:I46").ClearContents
Range("Voorblad!L9:M9").ClearContents
Range("Uren!Q11:Q30").ClearContents
Range("Uren!D11:E31").ClearContents
Range("Uren!Q1:R1").ClearContents
Range("Declaratie!C11:D31").ClearContents
Range("Declaratie!M11:M30").ClearContents
Range("Uren!E33").Value = Date
End Sub



Public Sub OpslBestand()
Dim NieuwFact As Variant
'kopiëren document als nieuwe factuur
ActiveSheet.Copy
NieuwFact = "C:\Users\Mili\Documents\Brank Scaffold\Test\Fact" & Range("Uren!Q3:R3").Value & ".xlsx"
ActiveWorkbook.SaveAs NieuwFact, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
VolgFact
End Sub


get Fact1
need - Week 00 2016-0000 Brank Scaffold

SamT
01-16-2016, 08:42 PM
Option Explicit

Public Sub OpslBestand()
Dim NieuwFact As Variant
Dim WeekNumber As String
Dim BrankScaffold As String

WeekNumber = DateDiff("ww", Date, vbFirstJan1) '0, 1, 2, etc
BrankScaffold = "Week " & WeekNumber & Format(Date, "yyyy-0000") & " Brank Scaffold"
'kopiëren document als nieuwe factuur
ActiveSheet.Copy
NieuwFact = "C:\Users\Mili\Documents\" & BrankScaffold & "\Test\Fact" & Range("Uren!Q3:R3").Value & ".xlsx"
ActiveWorkbook.SaveAs NieuwFact, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
VolgFact
End Sub

Sub WeekNumberCountFrom1()
Dim WeekNumber As String

WeekNumber = DateDiff("ww", Date, vbFirstJan1) + 1 '1, 2, 3, etc

End Sub

Sub TwoDigitWeekNumber()
Dim WeekNumber As String

WeekNumber = DateDiff("ww", Date, vbFirstJan1) '0, 1, 2, etc
If Len(WeekNumber) = 1 Then WeekNumber = "0" & WeekNumber '00, 01, 02, etc

End Sub

'Week 00 2016-0000 Brank Scaffold