publicado el 29 de enero2 años Sub FORMATO() ' ' TEXTO_COLUMNAS Macro ' Sheets("A-S1-001").Select Range("J2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 9), Array(10, 1)), TrailingMinusNumbers:=True Selection.NumberFormat = "h:mm:ss" Range("K2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("K2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]" Range("K2").Select Selection.Copy Range("K2:K1001").Select ActiveSheet.Paste Selection.End(xlUp).Select Columns("K:K").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("K1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$N$1001").AutoFilter Field:=11, Criteria1:= _ "00:00:00" Range("K1000").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("K1").Select ActiveSheet.Range("$A$1:$N$1001").AutoFilter Field:=11 Range("K2").Select ActiveWorkbook.Worksheets("A-S1-001").Sort.SortFields.Clear ActiveWorkbook.Worksheets("A-S1-001").Sort.SortFields.Add2 Key:=Range( _ "K2:K1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("A-S1-002").Sort .SetRange Range("A2:N1000") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.End(xlUp).Select Selection.End(xlToLeft).Select Sheets("A-S1-002").Select Range("J2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 9), Array(10, 1)), TrailingMinusNumbers:=True Selection.NumberFormat = "h:mm:ss" Range("K2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("K2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]" Range("K2").Select Selection.Copy Range("K2:K1001").Select ActiveSheet.Paste Selection.End(xlUp).Select Columns("K:K").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("K1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$N$1002").AutoFilter Field:=11, Criteria1:= _ "00:00:00" Range("K1000").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("K1").Select ActiveSheet.Range("$A$1:$N$1002").AutoFilter Field:=11 Range("K2").Select ActiveWorkbook.Worksheets("A-S1-004").Sort.SortFields.Clear ActiveWorkbook.Worksheets("A-S1-004").Sort.SortFields.Add2 Key:=Range( _ "K2:K1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("A-S1-002").Sort .SetRange Range("A2:N1000") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.End(xlUp).Select Selection.End(xlToLeft).Select Sheets("A-S1-003").Select Range("J2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 9), Array(10, 1)), TrailingMinusNumbers:=True Selection.NumberFormat = "h:mm:ss" Range("K2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("K2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]" Range("K2").Select Selection.Copy Range("K2:K1001").Select ActiveSheet.Paste Selection.End(xlUp).Select Columns("K:K").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("K1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$N$1001").AutoFilter Field:=11, Criteria1:= _ "00:00:00" Range("K1000").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Range("K1").Select ActiveSheet.Range("$A$1:$N$1000").AutoFilter Field:=11 Range("K2").Select ActiveWorkbook.Worksheets("A-S1-003").Sort.SortFields.Clear ActiveWorkbook.Worksheets("A-S1-003").Sort.SortFields.Add2 Key:=Range( _ "K2:K1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("A-S1-003").Sort .SetRange Range("A2:N1000") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Selection.End(xlUp).Select Selection.End(xlToLeft).Select Módulo3.bas
publicado el 30 de enero2 años Buenas, @DeadGoreRed Entiendo que ese código lo has grabado desde la grabadora de macros. Algo muy sencillo sería que identificaras una casilla en concreto de cada hoja (puede ser la misma o diferente) y chequearas si tiene o no datos, es decir, si no cumple la condición para que siga ejecutándose el código. Si no lo cumple, puedes incluir algo del tipo "Goto..." para pasar a otra zona del código donde se encuentre la siguiente hoja a chequear. La mejor opción en el caso de que todas tus hojas tuvieran una estructura de datos iguales es que incluyeras al inicio un bucle del tipo "For each" para que pasara por cada hoja del libro y en caso de cumplirse la condición ejecutara el código. Así no tendrías ese código tan largo para hacer lo mismo en cada hoja. Si te parece bien la idea, podemos intentar incluir esas pequeñas modificaciones y lo pruebas. Un saludo, Tese
Sub FORMATO()
'
' TEXTO_COLUMNAS Macro
'
Sheets("A-S1-001").Select
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "h:mm:ss"
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]"
Range("K2").Select
Selection.Copy
Range("K2:K1001").Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$1001").AutoFilter Field:=11, Criteria1:= _
"00:00:00"
Range("K1000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("K1").Select
ActiveSheet.Range("$A$1:$N$1001").AutoFilter Field:=11
Range("K2").Select
ActiveWorkbook.Worksheets("A-S1-001").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A-S1-001").Sort.SortFields.Add2 Key:=Range( _
"K2:K1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("A-S1-002").Sort
.SetRange Range("A2:N1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Sheets("A-S1-002").Select
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "h:mm:ss"
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]"
Range("K2").Select
Selection.Copy
Range("K2:K1001").Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$1002").AutoFilter Field:=11, Criteria1:= _
"00:00:00"
Range("K1000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("K1").Select
ActiveSheet.Range("$A$1:$N$1002").AutoFilter Field:=11
Range("K2").Select
ActiveWorkbook.Worksheets("A-S1-004").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A-S1-004").Sort.SortFields.Add2 Key:=Range( _
"K2:K1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("A-S1-002").Sort
.SetRange Range("A2:N1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Sheets("A-S1-003").Select
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "h:mm:ss"
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("K2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]"
Range("K2").Select
Selection.Copy
Range("K2:K1001").Select
ActiveSheet.Paste
Selection.End(xlUp).Select
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$1001").AutoFilter Field:=11, Criteria1:= _
"00:00:00"
Range("K1000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("K1").Select
ActiveSheet.Range("$A$1:$N$1000").AutoFilter Field:=11
Range("K2").Select
ActiveWorkbook.Worksheets("A-S1-003").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A-S1-003").Sort.SortFields.Add2 Key:=Range( _
"K2:K1000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("A-S1-003").Sort
.SetRange Range("A2:N1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Módulo3.bas