Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - paste special (transpose)
He hi,
It's been a while since I used VBA so the following might contain a very obvious error. I'd be very grateful if somebody could tell me what I do wrong in my code: Private Sub transpose() Worksheets("Sheet1").Copy Worksheets("Sheet2").PasteSpecial (transpose) End Sub I wrote this to make sure whole of sheet 1 is pasted in a transposed manner (thus columns become rows and other way around) into sheet 2. For in sheet 1 I enter new data, but to make sorting (I've only figured out how to do that by column) easier it has to be transposed before I do calculations with it. Doing this by hand means copying all of sheet 1 and use "paste special" and tick "transpose" to paste it in sheet 2. I'm not sure if it's right to write a private sub for this... or whether this code is the right one... Or wether I should write a loop like "copy row 1 and paste in column A" Greetz, Robin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - paste special (transpose)
Sub Macro1()
Sheets("Sheet1").Select Rows("1:256").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Rows("1:256").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub -- Gary''s Student - gsnu200826 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - paste special (transpose)
Thx :)
"Gary''s Student" wrote: Sub Macro1() Sheets("Sheet1").Select Rows("1:256").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Rows("1:256").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub -- Gary''s Student - gsnu200826 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - paste special (transpose)
Thank you, but for some reason "record macro" was unavailable - it was light
grey. I thought it wasn't allowed on the office computer I'm working on (no idea for what reason when VBA is allowed :S ). After closing and restarting the excel sheet I was allowed to record though. So thanks for your help - it made me try again :D. "Don Guillett" wrote: Try recording to see what you did wrong -- Don Guillett Microsoft MVP Excel SalesAid Software "paste special, macro, transpose" <paste special, macro, wrote in message ... He hi, It's been a while since I used VBA so the following might contain a very obvious error. I'd be very grateful if somebody could tell me what I do wrong in my code: Private Sub transpose() Worksheets("Sheet1").Copy Worksheets("Sheet2").PasteSpecial (transpose) End Sub I wrote this to make sure whole of sheet 1 is pasted in a transposed manner (thus columns become rows and other way around) into sheet 2. For in sheet 1 I enter new data, but to make sorting (I've only figured out how to do that by column) easier it has to be transposed before I do calculations with it. Doing this by hand means copying all of sheet 1 and use "paste special" and tick "transpose" to paste it in sheet 2. I'm not sure if it's right to write a private sub for this... or whether this code is the right one... Or wether I should write a loop like "copy row 1 and paste in column A" Greetz, Robin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro - paste special (transpose)
Private Sub transpose()
Worksheets("Sheet1").range("a1:a21").Copy Worksheets("Sheet2").range("a1").PasteSpecial _ Paste:=xlPasteAll, Transpose:=True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "paste special, macro, transpose" . com wrote in message ... Thank you, but for some reason "record macro" was unavailable - it was light grey. I thought it wasn't allowed on the office computer I'm working on (no idea for what reason when VBA is allowed :S ). After closing and restarting the excel sheet I was allowed to record though. So thanks for your help - it made me try again :D. "Don Guillett" wrote: Try recording to see what you did wrong -- Don Guillett Microsoft MVP Excel SalesAid Software "paste special, macro, transpose" <paste special, macro, wrote in message ... He hi, It's been a while since I used VBA so the following might contain a very obvious error. I'd be very grateful if somebody could tell me what I do wrong in my code: Private Sub transpose() Worksheets("Sheet1").Copy Worksheets("Sheet2").PasteSpecial (transpose) End Sub I wrote this to make sure whole of sheet 1 is pasted in a transposed manner (thus columns become rows and other way around) into sheet 2. For in sheet 1 I enter new data, but to make sorting (I've only figured out how to do that by column) easier it has to be transposed before I do calculations with it. Doing this by hand means copying all of sheet 1 and use "paste special" and tick "transpose" to paste it in sheet 2. I'm not sure if it's right to write a private sub for this... or whether this code is the right one... Or wether I should write a loop like "copy row 1 and paste in column A" Greetz, Robin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste special transpose macro | Excel Programming | |||
Transpose, Paste Special | Excel Discussion (Misc queries) | |||
how to create a copy/paste special/transpose macro? | Excel Discussion (Misc queries) | |||
Paste Special / transpose | Excel Discussion (Misc queries) | |||
Macro for one-click paste special transpose | Excel Programming |