Find word in a cell from multiple worksheets

Hello,

Bascially i would like a VBA code that can automatically look up a certain word in a cell from multiple sheets. But the difficult part is that the word in the cell depends on user's input and the sheets have different formats.

For example, if the user types in "Oct" in Sheet 4 (the "main" worksheet), then hopefully the macro can look for rows with the word "Oct" from Sheet 1. Then copy the 5th and the 6th cells from that row to a new worksheet (say sheet 5). Then, for sheet 2, it will as well look up "Oct" and copy the 10th and the 11st cells to sheet 5 (countinuing the list). For sheet 3, it will again look up "Oct" and copy the 9th and 10th cells to sheet 5 (countinuing the list).

Result:

(5th cell in sheet 1), (10th cell in sheet 2), (9th cell in sheet 3)will therefore listed vertically in a column with (6th cell in sheet 1), (11st cell in sheet 2), (10th cell in sheet 3) in the column next to it, in sheet 5.

Please could anyone help??


Thanks!!!!!