How to create a checkout list for a resort or hotel automatically using Excel VBA
Sigrun works in a resort where she enters the data of the guests on arrival. Data can include room number, first name, last name, ID proof, check-in date and checkout date. Based on the checkout dates which are in sheet ‘check-in’ she wishes to generate or create a checkout list in another worksheet called ‘checkout’. She wants to know how to do this using a formula.
You can use the IF function to get the first name and last name from the ‘checkin’ worksheet to the ‘checkout’ worksheet based on the comparison of the checkout date in the checkin worksheet with ‘today’s date. For example, in the checkout sheet if you have the headers first name and last name in the ranges A1 and B1 respectively, you can enter the following formulas in ranges A2 and B2:
In cell A2 in worksheet checkout: =IF(Checkin!F2=TODAY(), Checkin!B2, “”)
In cell B2 in worksheet checkout = IF(Checkin!F2=TODAY(),Checkin!C2,””)
Here the If function checks the checkout date in checkin sheet and compares it with the system data today and if true, gets the first name from the checkin sheet to the checkout sheet into the range A2 else it shows a blank. Similar action happens in checkout cell B2. However, wherever the data does not match those rows are left empty on autofill as shown in the video.
The more elegant solution to this problem is using VBA. The process of extraction of the data is fully automated whenever the user opens the file. The macro code is given below for ready reference:
Private Sub Workbook_Open()
Dim i As Long
Dim LastRow As Long
LastRow = Sheets(“Checkin”).Range(“A” & Rows.Count).End(xlUp).Row
Sheets(“Checkout”).Range(“A1”).Value = “Room Number”
Sheets(“Checkout”).Range(“B1”).Value = “First Name”
Sheets(“Checkout”).Range(“C1”).Value = “Last Name”
For i = 1 To LastRow
If Cells(i, “F”).Value = Date Then
Range(“A” & i & “:” & “B” & i & “:” & “C” & i).Copy Destination:=Sheets(“Checkout”).Range(“A” & Rows.Count).End(xlUp).Offset(1)
Explanation of the Excel VBA code
First we define the variables
Then we check the last row that has been used under column A
We cleart the ‘Checkout’ worksheet of any earlier data
Next we activate the ‘Checkin’ worksheet
At the same time we enter the headers into the ‘Checkout’ worksheet automatically. The headers include ‘Room Number’, ‘First Name’ and ‘last Name’. Of course, we can define more headers if we wish
Now using a ‘for next’ loop we find out the rows where the checkout dates match today’s date and copy the relevant or required data automatically from the checkin worksheet to the checkout worksheet to create our checkout list. This checkout list is generated automatically everyday whenever we open our workbook or excel file.
Once the list is generated we can also perform different kinds of formatting on the data automatically like we have ‘autofitted’ the extracted data in the columns.