StarBite207
05-19-2020, 12:00 PM
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):
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 (30th or 31st). 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 1st of the month to the team, the picked-up values of the 30th/31st 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 30th/31st 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!
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):
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 (30th or 31st). 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 1st of the month to the team, the picked-up values of the 30th/31st 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 30th/31st 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!