1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste data based on times and dates

Discussion in 'VBA Macros' started by excelnewbielearning, Oct 11, 2017.

  1. excelnewbielearning

    excelnewbielearning New Member

    Messages:
    19
    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.

    Attached Files:

  2. p45cal

    p45cal Well-Known Member

    Messages:
    841
    See attached.
    Includes:
    Code (vb):
    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
     

    Attached Files:

  3. excelnewbielearning

    excelnewbielearning New Member

    Messages:
    19
    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 !

  4. p45cal

    p45cal Well-Known Member

    Messages:
    841
    Attached.

    Attached Files:

Share This Page