What function should I input in Excel to implement this command, “If a cell in one sheet contains one word, then print a range of cells from another sheet right under it”?

Answer 1

There is no built-in Excel function which would accomplish this. However, you can insert an ‘Active X’ command button and a simple macro into your Excel workbook which at the click of a button would search for your keyword in Sheet 1 and then if the keyword is found, a command to print a range of cells in Sheet 2 would execute. In the VBA code below, the word being searched in Sheet 1 is “Keyword” and the print range in Sheet 2 is cells “A2:G20”. If the word you are searching for is expected to change over time, then I’d suggest editing the macro to have it either prompt you for the search word upon execution of the macro or have the macro reference a static cell in Sheet 1 where you can input your search word.

Sub Print_from_Keyword()

   

   Dim FindWord As String,

   Dim Found As Range

   FindWord = "Keyword"

   Set Found = Sheets("Sheet1").Cells.Find(What:=FindWord, _

                                           LookIn:=xlValues, _

                                           LookAt:=xlPart, _

                                           SearchOrder:=xlByRows, _

                                           SearchDirection:=xlNext, _

                                           MatchCase:=False)

                                       

   If Not Found Is Nothing Then

Sheet("Sheet2").Select

Range("A2:G20").Select

ActiveSheet.PageSetup.PrintArea = "A2:G20"

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _

:=True

   Else

       MsgBox "The Keyword was not found."

   End If

       

End Sub