Macro to insert rows based on Date and Time Cell

[Foxvh]

Hi,

i have a report which inputs rows of data into a (To Be Allocated) sheet and would like to have these sorted by Date and Time Allocated using a macro that places them below a merged row with the date as the header based on Date Cell (A:A) and then sorts the rows underneath the date header by time (E:E).

columns
Date (A:A)
Time (E:E)

any help or guidance would be much appreciated.

Thanks

 

[DanteAmor]

Just to clarify,
is your data like this? In which line do your data start? do you have headers

varios 10jul2020.xlsm
	A	B	C	D	E
1	DATE				TIME
2	08-jul				10:00
3	08-jul				11:00
4	08-jul				12:00
5	08-jul				13:00
6	08-jul				14:00
7	09-jul				15:00
8	09-jul				16:00
9	09-jul				17:00
10	10-jul				18:00
Start

Do you want this result?

varios 10jul2020.xlsm
	A	B	C	D	E
1	DATE				TIME
2	08-jul
3	08-jul				10:00
4	08-jul				11:00
5	08-jul				12:00
6	08-jul				13:00
7	08-jul				14:00
8	09-jul
9	09-jul				15:00
10	09-jul				16:00
11	09-jul				17:00
12	10-jul
13	10-jul				18:00
End

 

[Foxvh]

Hi Dante,

Apologies for the late response.

Yes the layout on the bottom one is correct.
The headers are columns A-N.
A,B,E will be blank for manual input (Date,Number,Time) where as every other column will have data input automatically from another sheet.

Thank you for your help.

 

[DanteAmor]

Sorry, but I still don't understand how your data is and how you want the output.
Could you put the examples now, use XL2BB tool, see my signature.

 

[Foxvh]

Sorry i'm using a work PC so i'm not able to install XL2BB i hope the below will be okay.

 

[DanteAmor]

You forgot the image of how your input data is, it is very obvious to you because you are viewing your data.
But before doing any code you could put an image of your sheet with data (replace your data with generic data)
What is the name of your data sheet and your destination sheet?

 

[Foxvh]

The below image is the sheet where data will be input from called ( To Be Allocated) the sheet it will be input to is called (Schedule)
wish i could just link the whole sheet but the data is confidential.

 

[DanteAmor]

I'm sorry I do not understand.
On the "To Be Allocated" sheet in column A you have the date 8jul.
On the "Schedule" sheet in row 2 you want "WEB 8/7".
- Where do I get "WEB"?
- Then there are 13 blank rows, are there always 13 rows between each date?

 

[Foxvh]

Sorry that's a typo it is supposed to be WEN 8/07 (DDD D/MM) which is the date display format, how that date is formatted doesn't really bother to me so can be changed if needed.

for each date there is a merged row (Purple) that the data lines are to be separated in too and a maximum of 15 lines of data below that date row.

 

[DanteAmor]

Try this

Sub Insert_Rows_Based_Date()
  Dim sh As Worksheet
  Dim i As Long, j As Long
  
  Set sh = Sheets("To Be Allocated")
  j = 2
  
  With Sheets("Schedule")
    .Rows("2:" & Rows.Count).Clear
    For i = 2 To sh.Range("A" & Rows.Count).End(3).Row
      With .Range("A" & j & ":N" & j)
        .Merge
        .Value = sh.Range("A" & i).Value
        .NumberFormat = "ddd d/mm"
        .HorizontalAlignment = xlCenter
      End With
      j = j + 16
    Next
  End With
End Sub