Pasar serie de datos a una sola fila

Como puedo ordenar la serie MUESTRAS en una sola GENERAL. (si solo fuesen 10 muestras, lo haría en forma manual, pero son 600.

Gracias

2 respuestas

Respuesta
1

Después de unas consultas y poner horas/traste en la silla de mi pc. Esta es la opción utilizada:

Sub acomoda()
'
' acomoda Macro
' Macro grabada el 23/10/2014 por Gilgamesh
'

'
Range("E11:E15").Select
Selection.EntireRow.Insert
Range("F10:J10").Select
Selection.Copy
Range("E11:E15").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E17:E21").Select
Selection.EntireRow.Insert
Range("F16:J16").Select
Selection.Copy
Range("E17:E21").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E23:E27").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
Range("F22:J22").Select
Selection.Copy
Range("E23:E27").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E29:E33").Select
Selection.EntireRow.Insert
ActiveWindow.SmallScroll Down:=18
Range("F28:J28").Select
Selection.Copy
Range("E29:E33").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E35:E39").Select
Selection.EntireRow.Insert
Range("F34:J34").Select
Selection.Copy
Range("E35:E39").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E41:E45").Select
Selection.EntireRow.Insert
Range("F40:J40").Select
Selection.Copy
Range("E41:E45").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E47:E51").Select
Selection.EntireRow.Insert
Range("F46:J46").Select
Selection.Copy
Range("E47:E51").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=18
Range("E53:E57").Select
Selection.EntireRow.Insert
Range("F52:J52").Select
Selection.Copy
Range("E53:E57").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E59:E63").Select
Selection.EntireRow.Insert
Range("F58:J58").Select
Selection.Copy
Range("E59:E63").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E65:E69").Select
Selection.EntireRow.Insert
Range("F64:J64").Select
Selection.Copy
Range("E65:E69").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E71:E75").Select
Selection.EntireRow.Insert
Range("F70:J70").Select
Selection.Copy
Range("E71:E75").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=21
Application.CutCopyMode = False
Range("E77:E81").Select
Selection.EntireRow.Insert
Range("F76:J76").Select
Selection.Copy
Range("E77:E81").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E83:E87").Select
Selection.EntireRow.Insert
Range("F82:J82").Select
Selection.Copy
Range("E83:E87").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E89:E93").Select
Selection.EntireRow.Insert
Range("F88:J88").Select
Selection.Copy
Range("E89:E93").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E95:E99").Select
Selection.EntireRow.Insert
Range("F94:J94").Select
Selection.Copy
Range("E95:E99").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=21
Application.CutCopyMode = False
Range("E101:E105").Select
Selection.EntireRow.Insert
Range("F100:J100").Select
Selection.Copy
Range("E101:E105").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E107:E111").Select
Selection.EntireRow.Insert
Range("F106:J106").Select
Selection.Copy
Range("E107:E111").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E113:E117").Select
Selection.EntireRow.Insert
Range("F112:J112").Select
Selection.Copy
Range("E113:E117").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E119:E123").Select
ActiveWindow.SmallScroll Down:=9
Selection.EntireRow.Insert
Range("F118:J118").Select
Selection.Copy
Range("E119:E123").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E125:E129").Select
Selection.EntireRow.Insert
Range("F124:J124").Select
Selection.Copy
Range("E125:E129").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=15
Range("E131:E135").Select
Selection.EntireRow.Insert
Range("F130:J130").Select
Selection.Copy
Range("E131:E135").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E137:E141").Select
Selection.EntireRow.Insert
Range("F136:J136").Select
Selection.Copy
Range("E137:E141").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E143:E147").Select
Selection.EntireRow.Insert
Range("F142:J142").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=12
Range("E143:E147").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E149:E153").Select
Selection.EntireRow.Insert
Range("F148:J148").Select
Selection.Copy
Range("E149:E153").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E155:E159").Select
Selection.EntireRow.Insert
ActiveWindow.SmallScroll Down:=12
Range("F154:J154").Select
Selection.Copy
Range("E155:E159").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E161:E165").Select
Selection.EntireRow.Insert
Range("F160:J160").Select
Selection.Copy
Range("E161:E165").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E167:E171").Select
Selection.EntireRow.Insert
Range("F166:J166").Select
Selection.Copy
Range("E167:E171").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=21
Range("E173:E177").Select
Application.CutCopyMode = False
Selection.EntireRow.Insert
Range("F172:J172").Select
Selection.Copy
Range("E173:E177").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E179:E183").Select
Selection.EntireRow.Insert
Range("F178:J178").Select
Selection.Copy
Range("E179:E183").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E185:E189").Select
Selection.EntireRow.Insert
Range("F184:J184").Select
Selection.Copy
Range("E185:E189").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E191:E195").Select
Selection.EntireRow.Insert
Range("F190:J190").Select
Selection.Copy
Range("E191:E195").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E197:E201").Select
Selection.EntireRow.Insert
Range("F196:J196").Select
Selection.Copy
Range("E197:E201").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E203:E207").Select
Selection.EntireRow.Insert
Range("F202:J202").Select
Selection.Copy
Range("E203:E207").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E209:E213").Select
Selection.EntireRow.Insert
Range("F208:J208").Select
Selection.Copy
Range("E209:E213").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E213").Select
ActiveWindow.SmallScroll Down:=15
Range("E215:E219").Select
Selection.EntireRow.Insert
Range("F214:J214").Select
Selection.Copy
Range("E215:E219").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E221:E225").Select
Selection.EntireRow.Insert
Range("F220:J220").Select
Selection.Copy
Range("E221:E225").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E227:E231").Select
Selection.EntireRow.Insert
Range("F226:J226").Select
Selection.Copy
Range("E227:E231").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=18
Application.CutCopyMode = False
Range("E233:E237").Select
Selection.EntireRow.Insert
Range("F232:J232").Select
Selection.Copy
Range("E233:E237").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E239:E243").Select
Selection.EntireRow.Insert
Range("F238:J238").Select
Selection.Copy
Range("E239:E243").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E245:E249").Select
Selection.EntireRow.Insert
Range("F244:J244").Select
Selection.Copy
Range("E245:E249").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=18
Application.CutCopyMode = False
Range("E251:E255").Select
Selection.EntireRow.Insert
Range("F250:J250").Select
Selection.Copy
Range("E251:E255").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E257:E261").Select
Selection.EntireRow.Insert
Range("F256:J256").Select
Selection.Copy
Range("E257:E261").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E263:E267").Select
Selection.EntireRow.Insert
Range("F262:J262").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=9
Range("E263:E267").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E269:E273").Select
Selection.EntireRow.Insert
Range("F268:J268").Select
Selection.Copy
Range("E269:E273").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E275").Select
ActiveWindow.SmallScroll Down:=15
Range("E275:E279").Select
Selection.EntireRow.Insert
Range("F274:J274").Select
Selection.Copy
Range("E275:E279").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E281:E285").Select
Selection.EntireRow.Insert
Range("F280:J280").Select
Selection.Copy
Range("E281:E285").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E287:E291").Select
Selection.EntireRow.Insert
ActiveWindow.SmallScroll Down:=15
Range("F286:J286").Select
Selection.Copy
Range("E287:E291").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E293:E297").Select
Selection.EntireRow.Insert
Range("F292:J292").Select
Selection.Copy
Range("E293:E297").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E299:E303").Select
Selection.EntireRow.Insert
Range("F298:J298").Select
Selection.Copy
Range("E299:E303").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E305:E309").Select
Selection.EntireRow.Insert
Range("F304:J304").Select
Selection.Copy
Range("E305:E309").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=15
Range("E311:E315").Select
Selection.EntireRow.Insert
Range("F310:J310").Select
Selection.Copy
Range("E311:E315").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E317:E321").Select
Selection.EntireRow.Insert
Range("F316:J316").Select
Selection.Copy
Range("E317:E321").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=18
Range("E323:E327").Select
Selection.EntireRow.Insert
Range("F322:J322").Select
Selection.Copy
Range("E323:E327").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E329:E333").Select
Selection.EntireRow.Insert
Range("F328:J328").Select
Selection.Copy
Range("E329:E333").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E335:E339").Select
Selection.EntireRow.Insert
Range("F334:J334").Select
Selection.Copy
Range("E335:E339").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E341:E345").Select
Selection.EntireRow.Insert
Range("F340:J340").Select
Selection.Copy
Range("E341:E345").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=18
Application.CutCopyMode = False
Range("E347:E351").Select
Selection.EntireRow.Insert
Range("F346:J346").Select
Selection.Copy
Range("E347:E351").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E353:E357").Select
Selection.EntireRow.Insert
Range("F352:J352").Select
Selection.Copy
Range("E353:E357").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E359:E363").Select
Selection.EntireRow.Insert
ActiveWindow.SmallScroll Down:=12
Range("F358:J358").Select
Selection.Copy
Range("E359:E363").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E365:E369").Select
Selection.EntireRow.Insert
Range("F364:J364").Select
Selection.Copy
Range("E365:E369").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E371:E375").Select
ActiveWindow.SmallScroll Down:=12
Selection.EntireRow.Insert
Range("F370:J370").Select
Selection.Copy
Range("E371:E375").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E377:E381").Select
Selection.EntireRow.Insert
Range("F376:J376").Select
Selection.Copy
Range("E377:E381").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E383").Select
ActiveWindow.SmallScroll Down:=15
Range("E383:E387").Select
Selection.EntireRow.Insert
Range("F382:J382").Select
Selection.Copy
Range("E383:E387").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E389:E393").Select
Selection.EntireRow.Insert
Range("F388:J388").Select
Selection.Copy
Range("E389:E393").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E395:E399").Select
Selection.EntireRow.Insert
ActiveWindow.SmallScroll Down:=9
Range("F394:J394").Select
Selection.Copy
Range("E395:E399").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E401:E405").Select
Selection.EntireRow.Insert
Range("F400:J400").Select
Selection.Copy
Range("E401:E405").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E407:E411").Select
Selection.EntireRow.Insert
Range("F406:J406").Select
Selection.Copy
Range("E407:E411").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E413:E417").Select
Selection.EntireRow.Insert
Range("F412:J412").Select
Selection.Copy
Range("E413:E417").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E419:E423").Select
Selection.EntireRow.Insert
Range("F418:J418").Select
Selection.Copy
Range("E419:E423").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E425:E429").Select
Selection.EntireRow.Insert
Range("F424:J424").Select
Selection.Copy
Range("E425:E429").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E431:E435").Select
Selection.EntireRow.Insert
Range("F430:J430").Select
Selection.Copy
Range("E431:E435").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E437:E441").Select
Selection.EntireRow.Insert
Range("F436:J436").Select
Selection.Copy
Range("E437:E441").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E443:E447").Select
Selection.EntireRow.Insert
Range("F442:J442").Select
Selection.Copy
Range("E443:E447").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E449:E453").Select
Selection.EntireRow.Insert
Range("F448:J448").Select
Selection.Copy
Range("E449:E453").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E455:E459").Select
Selection.EntireRow.Insert
Range("F454:J454").Select
Selection.Copy
Range("E455:E459").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E461:E465").Select
Selection.EntireRow.Insert
Range("F460:J460").Select
Selection.Copy
Range("E461:E465").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E467:E471").Select
Selection.EntireRow.Insert
Range("F466:J466").Select
Selection.Copy
Range("E467:E471").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E473:E477").Select
Selection.EntireRow.Insert
Range("F472:J472").Select
Selection.Copy
Range("E473:E477").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E479:E483").Select
Selection.EntireRow.Insert
Range("F478:J478").Select
Selection.Copy
Range("E479:E483").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E485:E489").Select
Selection.EntireRow.Insert
Range("F484:J484").Select
Selection.Copy
Range("E485:E489").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E491:E495").Select
Selection.EntireRow.Insert
Range("F490:J490").Select
Selection.Copy
Range("E491:E495").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E497:E501").Select
Selection.EntireRow.Insert
Range("F496:J496").Select
Selection.Copy
Range("E497:E501").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E503:E507").Select
Selection.EntireRow.Insert
Range("F502:J502").Select
Selection.Copy
Range("E503:E507").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E509:E513").Select
Selection.EntireRow.Insert
Range("F508:J508").Select
Selection.Copy
Range("E509:E513").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E515:E519").Select
Selection.EntireRow.Insert
Range("F514:J514").Select
Selection.Copy
Range("E515:E519").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E521:E525").Select
Selection.EntireRow.Insert
Range("F520:J520").Select
Selection.Copy
Range("E521:E525").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E527:E531").Select
Selection.EntireRow.Insert
Range("F526:J526").Select
Selection.Copy
Range("E527:E531").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E533:E537").Select
Selection.EntireRow.Insert
Range("F532:J532").Select
Selection.Copy
Range("E533:E537").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E539:E543").Select
Selection.EntireRow.Insert
Range("F538:J538").Select
Selection.Copy
Range("E539:E543").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E545:E549").Select
Selection.EntireRow.Insert
Range("F544:J544").Select
Selection.Copy
Range("E545:E549").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E551:E555").Select
Selection.EntireRow.Insert
Range("F550:J550").Select
Selection.Copy
Range("E551:E555").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E557:E561").Select
Selection.EntireRow.Insert
Range("F556:J556").Select
Selection.Copy
Range("E557:E561").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E563:E567").Select
Selection.EntireRow.Insert
Range("F562:J562").Select
Selection.Copy
Range("E563:E567").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E569:E573").Select
Selection.EntireRow.Insert
Range("F568:J568").Select
Selection.Copy
Range("E569:E573").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E575:E579").Select
Selection.EntireRow.Insert
Range("F574:J574").Select
Selection.Copy
Range("E575:E579").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E581:E585").Select
Selection.EntireRow.Insert
Range("F580:J580").Select
Selection.Copy
Range("E581:E585").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E587:E591").Select
Selection.EntireRow.Insert
Range("F586:J586").Select
Selection.Copy
Range("E587:E591").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E593:E597").Select
Selection.EntireRow.Insert
Range("F592:J592").Select
Selection.Copy
Range("E593:E597").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E599:E603").Select
Selection.EntireRow.Insert
Range("F598:J598").Select
Selection.Copy
Range("E599:E603").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E605:E609").Select
Selection.EntireRow.Insert
Range("F604:J604").Select
Selection.Copy
Range("E605:E609").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E611:E615").Select
Selection.EntireRow.Insert
Range("F610:J610").Select
Selection.Copy
Range("E611:E615").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("E617").Select
Application.CutCopyMode = False
Range("E617:E621").Select
Selection.EntireRow.Insert
Range("F616:J616").Select
Selection.Copy
Range("E617:E621").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=12
Application.CutCopyMode = False
Range("E623:E627").Select
Selection.EntireRow.Insert
Range("F622:J622").Select
Selection.Copy
Range("E623:E627").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E629:E633").Select
Selection.EntireRow.Insert
Range("F628:J628").Select
Selection.Copy
Range("E629:E633").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E635:E639").Select
Selection.EntireRow.Insert
Range("F634:J634").Select
Selection.Copy
Range("E635:E639").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E641:E645").Select
Selection.EntireRow.Insert
Range("F640:J640").Select
Selection.Copy
Range("E641:E645").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=3
Application.CutCopyMode = False
Range("E647:E651").Select
Selection.EntireRow.Insert
Range("F646:J646").Select
Selection.Copy
Range("E647:E651").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E653:E657").Select
Selection.EntireRow.Insert
Range("F652:J652").Select
Selection.Copy
Range("E653:E657").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E659:E663").Select
Selection.EntireRow.Insert
Range("F658:J658").Select
Selection.Copy
Range("E659:E663").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E665:E669").Select
Selection.EntireRow.Insert
Range("F664:J664").Select
Selection.Copy
Range("E665:E669").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E671:E675").Select
Selection.EntireRow.Insert
Range("F670:J670").Select
Selection.Copy
Range("E671:E675").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E677:E681").Select
Selection.EntireRow.Insert
Range("F676:J676").Select
Selection.Copy
Range("E677:E681").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=3
Application.CutCopyMode = False
Range("E683:E687").Select
Selection.EntireRow.Insert
Range("F682:J682").Select
Selection.Copy
Range("E683:E687").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E689:E693").Select
Selection.EntireRow.Insert
Range("F688:J688").Select
Selection.Copy
Range("E689:E693").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E695:E699").Select
Selection.EntireRow.Insert
Range("F694:J694").Select
Selection.Copy
Range("E695:E699").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=3
Application.CutCopyMode = False
Range("E701:E705").Select
Selection.EntireRow.Insert
Range("F700:J700").Select
Selection.Copy
Range("E701:E705").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E707:E711").Select
Selection.EntireRow.Insert
Range("F706:J706").Select
Selection.Copy
Range("E707:E711").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E713:E717").Select
Selection.EntireRow.Insert
Range("F712:J712").Select
Selection.Copy
Range("E713:E717").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=3
Application.CutCopyMode = False
Range("E719:E723").Select
Selection.EntireRow.Insert
Range("F718:J718").Select
Selection.Copy
Range("E719:E723").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Range("E725:E729").Select
Selection.EntireRow.Insert
Range("F724:J724").Select
Selection.Copy
Range("E725:E729").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E731:E735").Select
Selection.EntireRow.Insert
Range("F730:J730").Select
Selection.Copy
Range("E731:E735").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E737:E741").Select
Selection.EntireRow.Insert
Range("F736:J736").Select
Selection.Copy
Range("E737:E741").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SmallScroll Down:=6
Application.CutCopyMode = False
Range("E743:E747").Select
Selection.EntireRow.Insert
Range("F742:J742").Select
Selection.Copy
Range("E743:E747").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E749:E753").Select
Selection.EntireRow.Insert
Range("F748:J748").Select
Selection.Copy
Range("E749:E753").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Range("E755").Select
End Sub.

Respuesta

No se si lo he entendido del todo, pero prueba a:
1) Poner en D8:

=INDICE($B:$B;6*(COLUMNA()-3)+FILA()-6)

2) copiar y pegar esta fórmula hasta D13
3) Copiar el rango D8:D13 y pegarlo hacia la derecha tantas columnas como sean necesarias
Quizás haya que sustituir el punto y coma por una coma, en función del separador de listas que esté establecido en la configuración regional del equipo.

Muchas gracias por tu tiempo, lo probé y no me ha resultado.

Yo había entendido que se trataba de poner en columnas la columna B, pero si de lo que se trata es de poner en la columna B los datos de las columnas a partir de la D, la fórmula para B8 sería:

=INDICE($D$8:$BZ$13;RESIDUO(FILA()-8;6)+1;ENTERO((FILA()-8)/6)+1)

que habría que copiar y pegar hacia abajo hasta quese acaben los datos. También habría que modificar $BZ por la última columna con datos. libro con un ejemplo

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas

Ejecutar una macro dependiendo de otra macro?

Tengo una pequeña duda... Tengo dos botones con macros uno de ellos dice IMPRIMIR y otro dice REPARTO.. Lo que quiero es que no se pueda ejecutar el botón REPARTO si no se ha impreso nada osea si no le han dado clic al botón IMPRIMIR... En el botón...
3 respuestas

Pasar de fila a Columnas

Tengo este código que baje de la página de ustedes pero me coge la matriz y me la pasa toda a una fila y quisiera que fuera a una columna, no se que modificarle les agradezco que me ayduden a modificar el código y me copie todo a una sola columna...
1 respuesta

Macro copiar y pegar valores en columna filtrada

Necesito una macro que una vez filtrada una columna me copie una celda y pegue los valores en la misma, baje otra celda y haga lo mismo y así consecutivamente hasta el final. He grabado una macro con el grabador para que se den una idea. Sub Macro1()...
1 respuesta

Macro buscar-pegar

Me ha surgido un problema con una hoja de exel de registro de operaciones que estoy montando, me veo en la necesidad de ingresar datos de una hoja de calculo a otra y creo que la mejor opción es empleando una macro pero mi problema es que no se como...
1 respuesta

¿Cómo puedo cambiar celdas a formato de numero, por formato de celdas no me permite?

Solicito de su valiosa ayuda ya que no he podido crear una macro para Excel 2003 que me convierta un rango de celdas a numero, lo he intentado por formato de celdas y no pasa nada, la única forma es realizar el cambio manualmente para lo cual tengo...
1 respuesta

Macro en excel para transponer a una sola columna

Necesito realizar una macro que me haga lo siguiente: Tengo una serie de datos en filas en una hoja de excel. Quiero copiar un rango de celdas en dichas filas y pegarlos, transpuestos, y en el orden de las filas en una sola columna de una segunda hoja.
1 respuesta

Necesito macro para copiar y transponer celdas

Necesito una macro que haga lo siguiente; necesito copiar una cantidad de filas con celdas variables a partir de la columna B y trasponerlos enla columna A a partir de la celda A1, tengo la siguiente macro pero el problema es que me transpone una...
1 respuesta