Change Cell Value to create Summary Sheet

This may be very simple, but I'm new to macros and excel. I'm trying to figure out how to creat a macro that changes the cell location in the following formula from B9 to B10 and then moves down one cell and does the same, one hundred times...

The first cell shows ='Guest 1'!$B$9 and it should read ='Guest 1'$B$10
The next cell down shows ='Guest 2'!$B$9 and it should read ='Guest 2'$B$10

The bigger picture is that I have an Excel file with one months worth of check-in sheets at our hotel (100 sheets) and I'm trying to make summary pages that display a guests information horizontally. I've already edited one column so that each cell shows the guest's names all the way down. But I would like a quicker way to drag the formulas over and then quickly switch all of the cell references. We'll see if anybody followed that. Thanks for any help you may offer.