Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro and Copy and pasting special
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro and Copy and pasting special
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. "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro and Copy and pasting special
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 "Adam" wrote: 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. "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro and Copy and pasting special
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy-special pasting a table via an excel button into a word doc | Excel Programming | |||
Copy and Special pasting into an existing word template | Excel Programming | |||
Copy & Paste Special Macro | Excel Discussion (Misc queries) | |||
Copy Paste Special Macro | Excel Discussion (Misc queries) | |||
Excel 1004 Error When Pasting Special W/ Macro | Excel Discussion (Misc queries) |