Change row number in excel formulas / dates

[JorisM]

Hi all

I'm having a problem getting this macro going ...
I have a file with 2 sheets, 1 named "DagelijkseKassa" which contains dates next to other info, the other is named "Fiscale attesten" which contains cells with the same dates, but on different locations.

Row 8 of Fiscale attesten contains dates put together in weeks.

I already have a macro that, when the year is over, will write all the data in Fiscale attesten to a new sheet named Fiscale attesten & the corresponding year. Sheet "Fiscale attesten" will have it's data below row 8 deleted and will need to be prepped for the next year. This means the dates will have to be adapted.

So for 2017, "Fiscale attesten", range (F8:J8) contains the same dates as "DagelijkseKassa" range (C7:C11).
"Fiscale attesten", range (O8:S8) contains the same dates as "DagelijkseKassa" range (F7:F11).
"Fiscale attesten", range (Y8:AC8) contains the same dates as "DagelijkseKassa" range (C19:C23).
"Fiscale attesten", range (AH8:AL8) contains the same dates as "DagelijkseKassa" range (F19:F23).
"Fiscale attesten", range (AQ8:AU8) contains the same dates as "DagelijkseKassa" range (I19:I23). and so on, and so on
So the first 2 weeks are 1 period, the next 10 weeks are another period (eastern holidays and summer holidays)

When writing data to the new sheet and prepping "Fiscale attesten" for the next year the dates will have to change to :
for 2018, "Fiscale attesten", range (F8:J8) contains the same dates as "DagelijkseKassa" range (C31:C35).
"Fiscale attesten", range (O8:S8) contains the same dates as "DagelijkseKassa" range (F31:F35).
"Fiscale attesten", range (Y8:AC8) contains the same dates as "DagelijkseKassa" range (C43:C47).
"Fiscale attesten", range (AH8:AL8) contains the same dates as "DagelijkseKassa" range (F43:F47).
"Fiscale attesten", range (AQ8:AU8) contains the same dates as "DagelijkseKassa" range (I43:I47). and so on, and so on

And this will need to go on and on for other years. So there will be more dates under the lines in "DagelijkseKassa", each time the next year will be an equal amount (24) of rows lower than the year before.
So 2019 goes to rows 55:59 and 67:71, 2020 goes to ...

The macro for making the new tab is the following code (a button on "Fiscale attesten" triggers it:

 Sub NieuwTabblad()
 
    Dim jaar As Integer
    jaar = Range("B5").Value  'in this value the current year is mentioned
   
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(after:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Fiscale attesten " & jaar
    Range("A2").Select
    Sheets("Fiscale attesten").Select
    Cells.Select
    Selection.Copy
    ws.Select
    Cells.Select
    ActiveSheet.Paste
    Sheets("Fiscale attesten").Select
    Application.CutCopyMode = False
    Range("B5").Value = jaar + 1
    Rows("10:2000").Select
    Selection.ClearContents
    Range("B9").Select
End Sub

Thanks in advance.

 

[JorisM]

Bumper as i'm already on page 5