Copy and paste data based on times and dates

[excelnewbielearning]

Hello

Is there a way I could copy and paste booking details into a particular format list?

At the moment I'm manually entering the data from the bookings sheet onto the lists sheet in the layout I need. I'm trying to get every instance of the bookings into list form.

I have provided an example of the format I'm trying to get the raw data into. This list helps me to run other queries from various other tabs.

Please help if possible.

Many thanks and best wishes.

 

[p45cal]

See attached.
Includes:

Sub blah()
Set Selectn = Selection
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
NewSht.Cells(1).Resize(, 8).Value = Array("IDs", "Dates", "Hours", "Booked", "Reserved", "Not Available", "Available", "Status")
Set Destn = NewSht.Cells(2, 1)
For Each cll In Intersect(Selectn.EntireRow, Selectn.Parent.Columns(1)).Cells
  For dt = cll.Offset(, 1).Value To cll.Offset(, 2).Value
    If cll.Offset(, 3).Value = 0 And cll.Offset(, 4).Value = 0 Then
      'add single line to list:
      If Application.Trim(cll.Value) = "" Then Destn.Value = cll.Offset(, 5).Value Else Destn.Value = cll.Value
      Destn.Offset(, 1).Resize(, 7) = Array(dt, 0, IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "BOOKED", 1, 0), IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "RESERVED", 1, 0), 0, 0, cll.Offset(, 7).Value)
      Destn.Offset(, 2).NumberFormat = cll.Offset(, 3).NumberFormat
      Set Destn = Destn.Offset(1)
    Else
      'run through hourly:
      hr1 = cll.Offset(, 3).Value
      hr2 = cll.Offset(, 4).Value
      For hr = cll.Offset(, 3).Value To cll.Offset(, 4).Value + (1 / 1440) Step TimeValue("01:00:00")
        If Application.Trim(cll.Value) = "" Then Destn.Value = cll.Offset(, 5).Value Else Destn.Value = cll.Value
        Destn.Offset(, 1).Resize(, 7) = Array(dt, hr, IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "BOOKED", 1, 0), IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "RESERVED", 1, 0), 0, 0, cll.Offset(, 7).Value)
        Destn.Offset(, 2).NumberFormat = cll.Offset(, 3).NumberFormat
        Set Destn = Destn.Offset(1)
      Next hr
    End If
  Next dt
Next cll
NewSht.Columns("A:H").EntireColumn.AutoFit
End Sub

 

[excelnewbielearning]

See attached.
Includes:

Sub blah()
Set Selectn = Selection
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
NewSht.Cells(1).Resize(, 8).Value = Array("IDs", "Dates", "Hours", "Booked", "Reserved", "Not Available", "Available", "Status")
Set Destn = NewSht.Cells(2, 1)
For Each cll In Intersect(Selectn.EntireRow, Selectn.Parent.Columns(1)).Cells
  For dt = cll.Offset(, 1).Value To cll.Offset(, 2).Value
    If cll.Offset(, 3).Value = 0 And cll.Offset(, 4).Value = 0 Then
      'add single line to list:
      If Application.Trim(cll.Value) = "" Then Destn.Value = cll.Offset(, 5).Value Else Destn.Value = cll.Value
      Destn.Offset(, 1).Resize(, 7) = Array(dt, 0, IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "BOOKED", 1, 0), IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "RESERVED", 1, 0), 0, 0, cll.Offset(, 7).Value)
      Destn.Offset(, 2).NumberFormat = cll.Offset(, 3).NumberFormat
      Set Destn = Destn.Offset(1)
    Else
      'run through hourly:
      hr1 = cll.Offset(, 3).Value
      hr2 = cll.Offset(, 4).Value
      For hr = cll.Offset(, 3).Value To cll.Offset(, 4).Value + (1 / 1440) Step TimeValue("01:00:00")
        If Application.Trim(cll.Value) = "" Then Destn.Value = cll.Offset(, 5).Value Else Destn.Value = cll.Value
        Destn.Offset(, 1).Resize(, 7) = Array(dt, hr, IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "BOOKED", 1, 0), IIf(UCase(Application.Trim(cll.Offset(, 7).Value)) = "RESERVED", 1, 0), 0, 0, cll.Offset(, 7).Value)
        Destn.Offset(, 2).NumberFormat = cll.Offset(, 3).NumberFormat
        Set Destn = Destn.Offset(1)
      Next hr
    End If
  Next dt
Next cll
NewSht.Columns("A:H").EntireColumn.AutoFit
End Sub

Hello @p45cal

Thanks for that. That is the sort of thing I was looking for.

Just wondering, how do I get it to display my values on my current list sheet and not start a new sheet everytime I press the button?

Thank you and best wishes,


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[p45cal]

Attached.