Macro and Copy and pasting special

[Adam]

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

 

[Adam]

I need to get ride of the line of code that reads
Range("A1").Select

But it wont let me just paste special to the sheet, only to a specific cell.

 

[Joel]

Adam: when copying data the source and destination have to be the same type
range of data. If the source is a sheet, then the destination has to be a
sheet. The same applies to a range of cells, rows, column. I tried to copy
using cells (this is every cell on the sheet), and excel won't let me do
this. One problem is in Excel 2003 there are only 256 columns, whiule there
is 65536 rows. this won't transpose.

Excel also won't let you transpose to the same area. The only way I got it
to work was to transpose to a new sheet then copy the transposed data back to
the original sheet

Sub trans()
Worksheets.Add
ActiveSheet.Name = "temporary"
Application.CutCopyMode = False
Sheets("Sheet1").Rows("1:256").Copy
Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("temporary").Range("A1:Iv256").Copy _
Destination:=Sheets("Sheet1").Range("A1")

End Sub

 

[JLGWhiz]

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