Macro help for cut and pasting

diazonamide

New Member
Joined
Aug 16, 2013
Messages
1
Hello,
I'm new and in need of help to simplify a macro that I recorded. I don't know the language enough to be able to concentrate the essential commands to get the desired output for the macro. Ideally it would select a range (array) of cells in one column and automatically terminate once it reached a blank. Currently it does 40 cells, and 3 columns of data. Honestly if the macro could be repeated by selecting a desired column, then only one would do. (there's a sample test case and result at the very bottom)

Thanks in advanced

Sub Transpose_Data()
'
' Transpose_Data Macro
' Will take vertical replicates and organize them horizontal. Data must be in C9 and only 40 samples
'
' Keyboard Shortcut: Ctrl+t
'
ActiveWindow.SmallScroll Down:=0
Range("C10:G10").Select
Selection.EntireRow.Insert
Range("D9:D88").Select
Selection.EntireColumn.Insert
Range("F9:F88").Select
Selection.EntireColumn.Insert
Range("H9:H88").Select
Selection.EntireColumn.Insert
Range("J9:J88").Select
Selection.EntireColumn.Insert
Range("E12").Select
Selection.Cut
Range("D11").Select
ActiveSheet.Paste
Range("G12").Select
Selection.Cut
Range("F11").Select
ActiveSheet.Paste
Range("I12").Select
Selection.Cut
Range("H11").Select
ActiveSheet.Paste
Range("K12").Select
Selection.Cut
Range("J11").Select
ActiveSheet.Paste
Range("K14").Select
Selection.Cut
Range("J13").Select
ActiveSheet.Paste
Range("I14").Select
Selection.Cut
Range("H13").Select
ActiveSheet.Paste
Range("G14").Select
Selection.Cut
Range("F13").Select
ActiveSheet.Paste
Range("E14").Select
Selection.Cut
Range("D13").Select
ActiveSheet.Paste
Range("E16").Select
Selection.Cut
Range("D15").Select
ActiveSheet.Paste
Range("G16").Select
Selection.Cut
Range("F15").Select
ActiveSheet.Paste
Range("I16").Select
Selection.Cut
Range("H15").Select
ActiveSheet.Paste
Range("K16").Select
Selection.Cut
Range("J15").Select
ActiveSheet.Paste
Range("K18").Select
Selection.Cut
Range("J17").Select
ActiveSheet.Paste
Range("I18").Select
Selection.Cut
Range("H17").Select
ActiveSheet.Paste
Range("G18").Select
Selection.Cut
Range("F17").Select
ActiveSheet.Paste
Range("E18").Select
Selection.Cut
Range("D17").Select
ActiveSheet.Paste
Range("E20").Select
Selection.Cut
Range("D19").Select
ActiveSheet.Paste
Range("G20").Select
Selection.Cut
Range("F19").Select
ActiveSheet.Paste
Range("I20").Select
Selection.Cut
Range("H19").Select
ActiveSheet.Paste
Range("K20").Select
Selection.Cut
Range("J19").Select
ActiveSheet.Paste
Range("K22").Select
Selection.Cut
Range("J21").Select
ActiveSheet.Paste
Range("I22").Select
Selection.Cut
Range("H21").Select
ActiveSheet.Paste
Range("G22").Select
Selection.Cut
Range("F21").Select
ActiveSheet.Paste
Range("E22").Select
Selection.Cut
Range("D21").Select
ActiveSheet.Paste
Range("E24").Select
Selection.Cut
Range("D23").Select
ActiveSheet.Paste
Range("G24").Select
Selection.Cut
Range("F23").Select
ActiveSheet.Paste
Range("I24").Select
Selection.Cut
Range("H23").Select
ActiveSheet.Paste
Range("K24").Select
Selection.Cut
Range("J23").Select
ActiveSheet.Paste
Range("K26").Select
Selection.Cut
Range("J25").Select
ActiveSheet.Paste
Range("I26").Select
Selection.Cut
Range("H25").Select
ActiveSheet.Paste
Range("G26").Select
Selection.Cut
Range("F25").Select
ActiveSheet.Paste
Range("E26").Select
Selection.Cut
Range("D25").Select
ActiveSheet.Paste
Range("E28").Select
Selection.Cut
Range("D27").Select
ActiveSheet.Paste
Range("G28").Select
Selection.Cut
Range("F27").Select
ActiveSheet.Paste
Range("I28").Select
Selection.Cut
Range("H27").Select
ActiveSheet.Paste
Range("K28").Select
Selection.Cut
Range("J27").Select
ActiveSheet.Paste
Range("K30").Select
Selection.Cut
Range("J29").Select
ActiveSheet.Paste
Range("I30").Select
Selection.Cut
Range("H29").Select
ActiveSheet.Paste
Range("G30").Select
Selection.Cut
Range("F29").Select
ActiveSheet.Paste
Range("E30").Select
Selection.Cut
Range("D29").Select
ActiveSheet.Paste
Range("E32").Select
Selection.Cut
Range("D31").Select
ActiveSheet.Paste
Range("G32").Select
Selection.Cut
Range("F31").Select
ActiveSheet.Paste
Range("I32").Select
Selection.Cut
Range("H31").Select
ActiveSheet.Paste
Range("K32").Select
Selection.Cut
Range("J31").Select
ActiveSheet.Paste
Range("K34").Select
Selection.Cut
Range("J33").Select
ActiveSheet.Paste
Range("I34").Select
Selection.Cut
Range("H33").Select
ActiveSheet.Paste
Range("G34").Select
Selection.Cut
Range("F33").Select
ActiveSheet.Paste
Range("E34").Select
Selection.Cut
Range("D33").Select
ActiveSheet.Paste
Range("E36").Select
Selection.Cut
Range("D35").Select
ActiveSheet.Paste
Range("G36").Select
Selection.Cut
Range("F35").Select
Application.CutCopyMode = False
Range("G36").Select
Selection.Cut
Range("F35").Select
ActiveSheet.Paste
Range("I36").Select
Selection.Cut
Range("H35").Select
ActiveSheet.Paste
Range("K36").Select
Selection.Cut
Range("J35").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=33
Range("K38").Select
Selection.Cut
Range("J37").Select
ActiveSheet.Paste
Range("I38").Select
Selection.Cut
Range("H37").Select
ActiveSheet.Paste
Range("G38").Select
Selection.Cut
Range("F37").Select
ActiveSheet.Paste
Range("E38").Select
Selection.Cut
Range("D37").Select
ActiveSheet.Paste
Range("E40").Select
Selection.Cut
Range("D39").Select
ActiveSheet.Paste
Range("G40").Select
Selection.Cut
Range("F39").Select
ActiveSheet.Paste
Range("I40").Select
Selection.Cut
Range("H39").Select
ActiveSheet.Paste
Range("K40").Select
Selection.Cut
Range("J39").Select
ActiveSheet.Paste
Range("K42").Select
Selection.Cut
Range("J41").Select
ActiveSheet.Paste
Range("I42").Select
Selection.Cut
Range("H41").Select
ActiveSheet.Paste
Range("G42").Select
Selection.Cut
Range("F41").Select
ActiveSheet.Paste
Range("E42").Select
Selection.Cut
Range("D41").Select
ActiveSheet.Paste
Range("E44").Select
Selection.Cut
Range("D43").Select
ActiveSheet.Paste
Range("G44").Select
Selection.Cut
Range("F43").Select
ActiveSheet.Paste
Range("I44").Select
Selection.Cut
Range("H43").Select
ActiveSheet.Paste
Range("K44").Select
Selection.Cut
Range("J43").Select
ActiveSheet.Paste
Range("K46").Select
Selection.Cut
Range("J45").Select
ActiveSheet.Paste
Range("I46").Select
Selection.Cut
Range("H45").Select
ActiveSheet.Paste
Range("G46").Select
Selection.Cut
Range("F45").Select
ActiveSheet.Paste
Range("E46").Select
Selection.Cut
Range("D45").Select
ActiveSheet.Paste
Range("E48").Select
Selection.Cut
Range("D47").Select
ActiveSheet.Paste
Range("G48").Select
Selection.Cut
Range("F47").Select
ActiveSheet.Paste
Range("I48").Select
Selection.Cut
Range("H47").Select
ActiveSheet.Paste
Range("K48").Select
Selection.Cut
Range("J47").Select
ActiveSheet.Paste
Range("K50").Select
Selection.Cut
Range("J49").Select
ActiveSheet.Paste
Range("I50").Select
Selection.Cut
Range("H49").Select
ActiveSheet.Paste
Range("G50").Select
Selection.Cut
Range("F49").Select
ActiveSheet.Paste
Range("E50").Select
Selection.Cut
Range("D49").Select
ActiveSheet.Paste
Range("E52").Select
Selection.Cut
Range("D51").Select
ActiveSheet.Paste
Range("G52").Select
Selection.Cut
Range("F51").Select
ActiveSheet.Paste
Range("I52").Select
Selection.Cut
Range("H51").Select
ActiveSheet.Paste
Range("K52").Select
Selection.Cut
Range("J51").Select
ActiveSheet.Paste
Range("K54").Select
Selection.Cut
Range("J53").Select
ActiveSheet.Paste
Range("I54").Select
Selection.Cut
Range("H53").Select
ActiveSheet.Paste
Range("G54").Select
Selection.Cut
Range("F53").Select
ActiveSheet.Paste
Range("E54").Select
Selection.Cut
Range("D53").Select
ActiveSheet.Paste
Range("E56").Select
Selection.Cut
Range("D55").Select
ActiveSheet.Paste
Range("G56").Select
Selection.Cut
Range("F55").Select
ActiveSheet.Paste
Range("I56").Select
Selection.Cut
Range("H55").Select
ActiveSheet.Paste
Range("K56").Select
Selection.Cut
Range("J55").Select
ActiveSheet.Paste
Range("K58").Select
Selection.Cut
Range("J57").Select
ActiveSheet.Paste
Range("I58").Select
Selection.Cut
Range("H57").Select
ActiveSheet.Paste
Range("G58").Select
Selection.Cut
Range("F57").Select
ActiveSheet.Paste
Range("E58").Select
Selection.Cut
Range("D57").Select
ActiveSheet.Paste
Range("E60").Select
Selection.Cut
Range("D59").Select
ActiveSheet.Paste
Range("G60").Select
Selection.Cut
Range("F59").Select
ActiveSheet.Paste
Range("I60").Select
Selection.Cut
Range("H59").Select
ActiveSheet.Paste
Range("K60").Select
Selection.Cut
Range("J59").Select
ActiveSheet.Paste
Range("K62").Select
Selection.Cut
Range("J61").Select
ActiveSheet.Paste
Range("I62").Select
Selection.Cut
Range("H61").Select
ActiveSheet.Paste
Range("G62").Select
Selection.Cut
Range("F61").Select
ActiveSheet.Paste
Range("E62").Select
Selection.Cut
Range("D61").Select
ActiveSheet.Paste
Range("E64").Select
Selection.Cut
Range("D63").Select
ActiveSheet.Paste
Range("G64").Select
Selection.Cut
Range("F63").Select
ActiveSheet.Paste
Range("I64").Select
Selection.Cut
Range("H63").Select
ActiveSheet.Paste
Range("K64").Select
Selection.Cut
Range("J63").Select
ActiveSheet.Paste
Range("K66").Select
Selection.Cut
Range("J65").Select
ActiveSheet.Paste
Range("I66").Select
Selection.Cut
Range("H65").Select
ActiveSheet.Paste
Range("G66").Select
Selection.Cut
Range("F65").Select
ActiveSheet.Paste
Range("E66").Select
Selection.Cut
Range("D65").Select
ActiveSheet.Paste
Range("E68").Select
Selection.Cut
Range("D67").Select
ActiveSheet.Paste
Range("G68").Select
Selection.Cut
Range("F67").Select
ActiveSheet.Paste
Range("I68").Select
Selection.Cut
Range("H67").Select
ActiveSheet.Paste
Range("K68").Select
Selection.Cut
Range("J67").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=27
Range("K70").Select
Selection.Cut
Range("J69").Select
ActiveSheet.Paste
Range("I70").Select
Selection.Cut
Range("H69").Select
ActiveSheet.Paste
Range("G70").Select
Selection.Cut
Range("F69").Select
ActiveSheet.Paste
Range("E70").Select
Selection.Cut
Range("D69").Select
ActiveSheet.Paste
Range("E72").Select
Selection.Cut
Range("D71").Select
ActiveSheet.Paste
Range("G72").Select
Selection.Cut
Range("F71").Select
ActiveSheet.Paste
Range("I72").Select
Selection.Cut
Range("H71").Select
ActiveSheet.Paste
Range("K72").Select
Selection.Cut
Range("J71").Select
ActiveSheet.Paste
Range("K74").Select
Selection.Cut
Range("J73").Select
ActiveSheet.Paste
Range("I74").Select
Selection.Cut
Range("H73").Select
ActiveSheet.Paste
Range("G74").Select
Selection.Cut
Range("F73").Select
ActiveSheet.Paste
Range("E74").Select
Selection.Cut
Range("D73").Select
ActiveSheet.Paste
Range("E76").Select
Selection.Cut
Range("D75").Select
ActiveSheet.Paste
Range("G76").Select
Selection.Cut
Range("F75").Select
ActiveSheet.Paste
Range("I76").Select
Selection.Cut
Range("H75").Select
ActiveSheet.Paste
Range("K76").Select
Selection.Cut
Range("J75").Select
ActiveSheet.Paste
Range("K78").Select
Selection.Cut
Range("J77").Select
ActiveSheet.Paste
Range("I78").Select
Selection.Cut
Range("H77").Select
ActiveSheet.Paste
Range("G78").Select
Selection.Cut
Range("F77").Select
ActiveSheet.Paste
Range("E78").Select
Selection.Cut
Range("D77").Select
ActiveSheet.Paste
Range("E80").Select
Selection.Cut
Range("D79").Select
ActiveSheet.Paste
Range("G80").Select
Selection.Cut
Range("F79").Select
ActiveSheet.Paste
Range("I80").Select
Selection.Cut
Range("H79").Select
ActiveSheet.Paste
Range("K80").Select
Selection.Cut
Range("J79").Select
ActiveSheet.Paste
Range("K82").Select
Selection.Cut
Range("J81").Select
ActiveSheet.Paste
Range("I82").Select
Selection.Cut
Range("H81").Select
ActiveSheet.Paste
Range("G82").Select
Selection.Cut
Range("F81").Select
ActiveSheet.Paste
Range("E82").Select
Selection.Cut
Range("D81").Select
ActiveSheet.Paste
Range("E84").Select
Selection.Cut
Range("D83").Select
ActiveSheet.Paste
Range("G84").Select
Selection.Cut
Range("F83").Select
ActiveSheet.Paste
Range("I84").Select
Selection.Cut
Range("H83").Select
ActiveSheet.Paste
Range("K84").Select
Selection.Cut
Range("J83").Select
ActiveSheet.Paste
Range("K86").Select
Selection.Cut
Range("J85").Select
ActiveSheet.Paste
Range("I86").Select
Selection.Cut
Range("H85").Select
ActiveSheet.Paste
Range("G86").Select
Selection.Cut
Range("F85").Select
ActiveSheet.Paste
Range("E86").Select
Selection.Cut
Range("D85").Select
ActiveSheet.Paste
Range("E88").Select
Selection.Cut
Range("D87").Select
ActiveSheet.Paste
Range("G88").Select
Selection.Cut
Range("F87").Select
ActiveSheet.Paste
Range("I88").Select
Selection.Cut
Range("H87").Select
ActiveSheet.Paste
Range("K88").Select
Selection.Cut
Range("J87").Select
ActiveSheet.Paste
Range("C88").Select
ActiveCell.FormulaR1C1 = ""
Range("C86").Select
ActiveCell.FormulaR1C1 = ""
Range("C84").Select
ActiveCell.FormulaR1C1 = ""
Range("C82").Select
ActiveCell.FormulaR1C1 = ""
Range("C80").Select
ActiveCell.FormulaR1C1 = ""
Range("C78").Select
ActiveCell.FormulaR1C1 = ""
Range("C76").Select
ActiveCell.FormulaR1C1 = ""
Range("C74").Select
ActiveCell.FormulaR1C1 = ""
Range("C72").Select
ActiveCell.FormulaR1C1 = ""
Range("C70").Select
ActiveCell.FormulaR1C1 = ""
Range("C68").Select
ActiveCell.FormulaR1C1 = ""
Range("C66").Select
ActiveCell.FormulaR1C1 = ""
Range("C64").Select
ActiveCell.FormulaR1C1 = ""
Range("C62").Select
ActiveCell.FormulaR1C1 = ""
Range("C60").Select
ActiveCell.FormulaR1C1 = ""
Range("C58").Select
ActiveCell.FormulaR1C1 = ""
Range("C56").Select
ActiveCell.FormulaR1C1 = ""
Range("C54").Select
ActiveCell.FormulaR1C1 = ""
Range("C52").Select
ActiveCell.FormulaR1C1 = ""
Range("C50").Select
ActiveCell.FormulaR1C1 = ""
Range("C48").Select
ActiveCell.FormulaR1C1 = ""
Range("C46").Select
ActiveCell.FormulaR1C1 = ""
Range("C44").Select
ActiveCell.FormulaR1C1 = ""
Range("C42").Select
ActiveCell.FormulaR1C1 = ""
Range("C40").Select
ActiveCell.FormulaR1C1 = ""
Range("C38").Select
ActiveCell.FormulaR1C1 = ""
Range("C36").Select
ActiveCell.FormulaR1C1 = ""
Range("C34").Select
ActiveCell.FormulaR1C1 = ""
Range("C32").Select
ActiveCell.FormulaR1C1 = ""
Range("C30").Select
ActiveCell.FormulaR1C1 = ""
Range("C28").Select
ActiveCell.FormulaR1C1 = ""
Range("C26").Select
ActiveCell.FormulaR1C1 = ""
Range("C24").Select
ActiveCell.FormulaR1C1 = ""
Range("C22").Select
ActiveCell.FormulaR1C1 = ""
Range("C20").Select
ActiveCell.FormulaR1C1 = ""
Range("C18").Select
ActiveCell.FormulaR1C1 = ""
Range("C16").Select
ActiveCell.FormulaR1C1 = ""
Range("C14").Select
ActiveCell.FormulaR1C1 = ""
Range("C12").Select
ActiveCell.FormulaR1C1 = ""
Range("C12:K12").Select
Range("K12").Activate
Selection.Delete Shift:=xlUp
Range("C13:K13").Select
Range("K13").Activate
Selection.Delete Shift:=xlUp
Range("C14:K14").Select
Range("K14").Activate
Selection.Delete Shift:=xlUp
Range("C15:K15").Select
Selection.Delete Shift:=xlUp
Range("C16:K16").Select
Range("K16").Activate
Selection.Delete Shift:=xlUp
Range("C17:K17").Select
Selection.Delete Shift:=xlUp
Range("C18:K18").Select
Selection.Delete Shift:=xlUp
Range("C19:K19").Select
Selection.Delete Shift:=xlUp
Range("C20:K20").Select
Selection.Delete Shift:=xlUp
Range("C21:K21").Select
Selection.Delete Shift:=xlUp
Range("C22:K22").Select
Selection.Delete Shift:=xlUp
Range("C23:K23").Select
Selection.Delete Shift:=xlUp
Range("C24:K24").Select
Selection.Delete Shift:=xlUp
Range("C25:K25").Select
Selection.Delete Shift:=xlUp
Range("C26:K26").Select
Selection.Delete Shift:=xlUp
Range("C27:K27").Select
Selection.Cut
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("C28:K28").Select
Selection.Delete Shift:=xlUp
Range("C29:K29").Select
Selection.Delete Shift:=xlUp
Range("C30:K30").Select
Selection.Delete Shift:=xlUp
Range("C31:K31").Select
Selection.Delete Shift:=xlUp
Range("C32:K32").Select
Selection.Delete Shift:=xlUp
Range("C33:K33").Select
Selection.Delete Shift:=xlUp
Range("C34:K34").Select
Selection.Delete Shift:=xlUp
Range("C35:K35").Select
Selection.Delete Shift:=xlUp
Range("C36:K36").Select
Selection.Delete Shift:=xlUp
Range("C37:K37").Select
Selection.Delete Shift:=xlUp
Range("C38:K38").Select
Selection.Delete Shift:=xlUp
Range("C39:K39").Select
Selection.Delete Shift:=xlUp
Range("C40:K40").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=21
Range("C41:K41").Select
Selection.Delete Shift:=xlUp
Range("C42:K42").Select
Selection.Delete Shift:=xlUp
Range("C43:K43").Select
Selection.Delete Shift:=xlUp
Range("C44:K44").Select
Selection.Delete Shift:=xlUp
Range("C45:K45").Select
Selection.Delete Shift:=xlUp
Range("C46:K46").Select
Selection.Delete Shift:=xlUp
Range("C47:K47").Select
Selection.Delete Shift:=xlUp
Range("C48:K48").Select
Selection.Delete Shift:=xlUp
Range("C49:K49").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-27
Range("D10").Select
ActiveCell.FormulaR1C1 = "REP 1"
Range("E10").Select
ActiveCell.FormulaR1C1 = "REP 2"
Range("D10:E10").Select
Selection.Copy
Range("F10").Select
ActiveSheet.Paste
Range("H10").Select
ActiveSheet.Paste
Range("J10").Select
ActiveSheet.Paste
Range("L12").Select
End Sub

sampleresult
12
13
24
25
36
37
47
48

<COLGROUP><COL style="WIDTH: 54pt" span=2 width=72><TBODY>
</TBODY>
Goal of macro should look like below
sampleresult
REP 1REP 2
123
245
367
478

<COLGROUP><COL style="WIDTH: 54pt" span=3 width=72><TBODY>
</TBODY>

<TBODY>
</TBODY>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,132,781
Messages
5,655,253
Members
418,183
Latest member
skaufman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top