How to create a checkout list automatically using Excel VBA

Home More Videos

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' 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 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)
End If
End Sub

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.

Watch the video on youtube