VBA/Macro Copy N Times N=Days in Dateformat

[tobi97]

Hi guys,
Im new to using excel behind like some standard stuff.
So i have a cell for a date range (green marked).
Now i want the rows 7 to 27 copied underneath N times.
N = the days in the date range.
Example:
15.8.2023 - 20.8.2023 = 6 days
So rows 7 to 27 copied 6 times underneath.

As little special but not so much important:
In every copy the Date (blue marked) should change to the according day in the date range.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future , I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You have not said exactly where to paste the copies but give this a try with a copy of your workbook.
I have assumed what looks like merged cells E7:U7 have an appropriate date format applied.

Sub Copy_Range_N_Times()
  Dim DateStartEnd As Variant
  Dim i As Long
  
  DateStartEnd = Split(Range("B4").Value, "-")
  Application.ScreenUpdating = False
  For i = 1 To DateValue(DateStartEnd(1)) - DateValue(DateStartEnd(0)) + 1
    Range("B7:V27").Copy Destination:=Range("B" & 7 + 22 * i)
    Range("E" & 7 + 22 * i).Value = DateValue(DateStartEnd(0)) + i - 1
  Next i
  Application.ScreenUpdating = True
End Sub

 

[tobi97]

Welcome to the MrExcel board!

For the future , I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

You have not said exactly where to paste the copies but give this a try with a copy of your workbook.
I have assumed what looks like merged cells E7:U7 have an appropriate date format applied.

Sub Copy_Range_N_Times()
  Dim DateStartEnd As Variant
  Dim i As Long
 
  DateStartEnd = Split(Range("B4").Value, "-")
  Application.ScreenUpdating = False
  For i = 1 To DateValue(DateStartEnd(1)) - DateValue(DateStartEnd(0)) + 1
    Range("B7:V27").Copy Destination:=Range("B" & 7 + 22 * i)
    Range("E" & 7 + 22 * i).Value = DateValue(DateStartEnd(0)) + i - 1
  Next i
  Application.ScreenUpdating = True
End Sub

Hi!
Works perfect
Thanks!!
I just have small follow up question.
If i want it to go/copy it to the right side of it not underneath which value do i need to change in your code?

 

[Peter_SSs]

If i want it to go/copy it to the right side of it not underneath which value do i need to change in your code?

Sub Copy_Range_N_Times_v2()
  Dim DateStartEnd As Variant
  Dim i As Long
  
  DateStartEnd = Split(Range("B4").Value, "-")
  Application.ScreenUpdating = False
  For i = 1 To DateValue(DateStartEnd(1)) - DateValue(DateStartEnd(0)) + 1
    Range("B7:V27").Copy Destination:=Cells(7, 2 + 22 * i)
    Cells(7, 5 + 22 * i).Value = DateValue(DateStartEnd(0)) + i - 1
  Next i
  Application.ScreenUpdating = True
End Sub