How to Copy-Paste from a dynamic range to fixed one based on cell value using VBA?

Hi all,

I need your help please. I have a pick up report to capture each day rooms and revenue pick up once the day is closed.
I have attached the entire excel workbook as there is no way that my procedure would be clearly understood with screenshots and with the requirement below (Please open the workbook before reading the below):

Attachment 26701

In sheet “Processor 2”, there are two ranges. Range C5 to J35 referred as RN and C41 to J71 referred as REV. Both of these ranges have rows each numbered to reflect the number of days in the month and each row will populate values whenever the day of the month changes in sheet “TD” cell value H4 (You need to try this out on the sheet to understand it clearly).

For example, when the day “8” is selected in H4 in sheet “TD”, all the numbers found in range F132 to M132 in sheet “DV” will appear in row 11 next to day “7” in sheet “Processor 2”. Next day, when the value changes to day “9”, then the figures found in sheet “DV” would appear in row 12 in “Processor 2” and row 11 will return back to “0”, and so on and so forth. On this stage, no vba or formula is required.

In order to capture day to day values changes, I have created another range for RN and for REV next to the dynamic ones. This is where I need to capture my fixed picked up value through a VBA. Continuing the above example, when I’m on day “8”, then the figures of day “7” in row 11 range C11 to J11 will then need to be copied to row 11 range M11 to T11 and so on.

The challenge here is found in the last day of the month (30^th or 31^st). If the above VBA will work, then it will copy the previous day which is correct. But when I’m closing and reporting this report on the 1^st of the month to the team, the picked-up values of the 30^th/31^st will not be there. So, I created an additional value in cell H4 in “TD” sheet that says “closed”, when this is selected, then I want the last day of the month 30^th/31^st depending on the month days count to be captured and copied in the fixed range. Not sure how this can be done and I'm open to any suggestions or additions you find best to do or add on the excel sheet.

There’s a step two that requires another VBA for the same workbook. But, it will take another paragraph to explain :bug:, so I appreciate assisting me on the above VBA before moving to the second one : pray2:

Appreciate your help!

Hi

If I understand this properly then can you copy the values across every day on a rolling basis? Something like:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    If Target.Address = "$H$4" Then
        For i = 5 To 35
            With Sheet15
                If .Cells(i, 3) <> 0 Then
                    .Range(.Cells(i, "C"), .Cells(i, "J")).Copy
                    .Cells(i, "M").PasteSpecial xlPasteValues
                End If
            End With
        Next
    End If
End Sub

---

I wish I had a bit more time, but I'm thrashed out at the moment.

Cheers

Hi,

So the VBA is doing the job for all days but it does not address the challenge of the 30 or 31 especially when the month is 30 days as in the attached example and I will have the same issue when I'm switching to May.

Also, when I'm selecting the day "2" in the sheet, it is correct that the VBA is copying in the range M5 to T5 but it is also copying in range M35 to T35 when the month count is 30 ..

Additionally, I need the same VBA rule to apply for range "REV" M41 to T71

:help

anyone who can help?