Macro help for cut and pasting

D

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You must log in or register to reply here.

Similar threads

S
  • Solved
Copy row 2 and paste it after last row of another worksheet
Replies
9
Views
877
rollis13
rollis13
P
  • Question
Help with Vba Code
Replies
3
Views
162
Coyotex3
C
G
  • Question
Macro to cut entire row form table depending on the value of column "M" and paste on another table
Replies
3
Views
306
kevin9999
K
C
  • Solved
Replace fixed location in MACRO with user selection
Replies
3
Views
126
CordingBags
C
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
210
Coco1
C

Forum statistics

Threads
1,215,063
Messages
6,122,927
Members
449,094
Latest member
teemeren

Share this page

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
Back
Top