VBA Help - to copy cell contents to used range and loop

[bbalch]

I need some VBA to copy the contents of cell "A3" and paste them in Range H7: "the last row used in column F" and then loop to the next worksheet.

What I'm trying to accomplish: I'm turning individual reports (tabs) into a consolidated table (a summary tab), the department name is currently in cell A3 on each tab and I would like for it to end up in column H for each row of the report so that I can keep the data connected to the department code in the consolidated table.

Your help is greatly appreciated.

Thanks,
Ben