I have about 500 sheets of data, Each Sheet has the exact same setup of the
data, and each sheet is a unique site. I need to set up a macro that will
copy and paste special, transpose the data onto a new sheet, in an dffort to
stack the data vertically on a new sheet.

i have been playing around with it. But it wont let me do this code. I
wanna have it do

Range("B9:H31").Select
Selection.Copy
Windows("Book2").Activate
Range("A1").Select
ActiveSheet.PasteSpecial , Transpose:=True
ActiveCell.Offset(7, 0).Select
Windows("Advocate-AICU-Aurora-Baptist 2005
BenchDVTProphylaxis.xls").Activate
ActiveSheet.Next.Select

It wont let me do this.

Any Ideas?

Thanks,

-Adam