You just need to make sure your destination is fully qualified. You can
substitute the workbooks names and sheets names or index in the code below
and it should work the way you want.

Sub trsps()
Set cSh = Workbooks(1).Sheets(1)
Set pSh = Workbooks(2).Sheets(1)
cSh.Range("B9:H31").Copy
pSh.Range("A1").PasteSpecial Transpose:=True
End Sub

"Adam" wrote:

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