How to create a checkout list 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, checkin date and checkout
date. Based on the checkout dates which are in sheet 'checkin'
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, whereevr 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