¿Cómo modificar macro?

Esta macro lo que hace es básicamente copiar datos de una hoja a las otras dos hojas en un libro excel, pero cuando hay una formula copia la formula, ¿Cómo puedo hacer para que en lugar de copiar la formula, sólo copie los datos?.
Gracias por su tiempo
Dios le bendiga

Sub GENERAR()
'
' GENERAR Macro
'
' Acceso directo: CTRL+r
'
'Dim X As Integer
Sheets("DATOS").Select
'X = 0
Range("A2").Select
Do Until IsEmpty(ActiveCell)
 Selection.Copy
 Sheets("CABECERA").Select
 Range("A2").Select
 ActiveSheet.Paste
 Range("B2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("C2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("D2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 8).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("E2").Select
 ActiveCell.FormulaR1C1 = "F"
 Range("F2").Select
 ActiveCell.FormulaR1C1 = "0"
 Range("G2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("H2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -3).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("I2").Select
 ActiveCell.FormulaR1C1 = "V"
 Range("J2").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("A2:B2").Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("C2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "0001"
 Range("D2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -6).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("E2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "121201"
 Range("F2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 3).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("G2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "H"
 Range("H2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 3).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("I2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -5).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("J2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("D2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("K2").Select
 ActiveSheet.Paste
 Range("M2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = " " 'PARA CAMBIAR AREA
 Range("N2").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("O2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 7).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("A2:B2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("A3").Select
 ActiveSheet.Paste
 Range("C3").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "0002"
 Range("D2").Select
 Selection.Copy
 Range("D3").Select
 ActiveSheet.Paste
 Range("E3").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -2).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("F2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("F3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("G3").Select
 ActiveCell.FormulaR1C1 = "D"
 Range("H2").Select
 Selection.Copy
 Range("H3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("I2").Select
 Selection.Copy
 Range("I3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("J2").Select
 Selection.Copy
 Range("J3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("K2").Select
 Selection.Copy
 Range("K3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("M3").Select
 ActiveCell.FormulaR1C1 = " "
 Range("N3").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("O2").Select
 Selection.Copy
 Range("O3").Select
 ActiveSheet.Paste
 Range("A2:B2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("A4").Select
 ActiveSheet.Paste
 Range("C4").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "0003"
 Range("D3").Select
 Selection.Copy
 Range("D4").Select
 ActiveSheet.Paste
 Range("E4").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -2).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("F3").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("F4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("G4").Select
 ActiveCell.FormulaR1C1 = "D"
 Range("H3").Select
 Selection.Copy
 Range("H4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("I3").Select
 Selection.Copy
 Range("I4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("J3").Select
 Selection.Copy
 Range("J4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("K3").Select
 Selection.Copy
 Range("K4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("M4").Select
 ActiveCell.FormulaR1C1 = " "
 Range("N4").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("O3").Select
 Selection.Copy
 Range("O4").Select
 ActiveSheet.Paste
 Rows("2:2").Select
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Range("A2").Select
...

1 respuesta

Respuesta
1

Espero estés de maravilla!

La linea que pega "valores" seria la siguiente:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

Felicitaciones por la macro que has realizado.

Bendiciones!

Lucas.

Esta macro la desarrollo un amigo, estoy tratando de entender su contenido

Por favor me podrías indicar si estoy en lo correcto

Sub PRUEBA()
'
' PRUEBA Macro
'
'Dim X As Integer
Sheets("DATOS").Select
'X = 0
Range("A2").Select
Do Until IsEmpty(ActiveCell)
 Selection.Copy
 Sheets("CABECERA").Select
 Range("A2").Select
 ActiveSheet.Paste
 Range("B2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("C2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("D2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 8).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("E2").Select
 ActiveCell.FormulaR1C1 = "F"
 Range("F2").Select
 ActiveCell.FormulaR1C1 = "0"
 Range("G2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 Range("H2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -3).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("CABECERA").Select
 ActiveSheet.Paste
 Range("I2").Select
 ActiveCell.FormulaR1C1 = "V"
 Range("J2").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("A2:B2").Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("C2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "0001"
 Range("D2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -6).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("E2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "121201"
 Range("F2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 3).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("G2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "H"
 Range("H2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 3).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("I2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -5).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("J2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 1).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("D2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("K2").Select
 ActiveSheet.Paste
 Range("M2").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = " " 'PARA CAMBIAR AREA
 Range("N2").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("O2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, 7).Select
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("A2:B2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("A3").Select
 ActiveSheet.Paste
 Range("C3").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "0002"
 Range("D2").Select
 Selection.Copy
 Range("D3").Select
 ActiveSheet.Paste
 Range("E3").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -2).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("F2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("F3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("G3").Select
 ActiveCell.FormulaR1C1 = "D"
 Range("H2").Select
 Selection.Copy
 Range("H3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("I2").Select
 Selection.Copy
 Range("I3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("J2").Select
 Selection.Copy
 Range("J3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("K2").Select
 Selection.Copy
 Range("K3").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("M3").Select
 ActiveCell.FormulaR1C1 = " "
 Range("N3").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("O2").Select
 Selection.Copy
 Range("O3").Select
 ActiveSheet.Paste
 Range("A2:B2").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("A4").Select
 ActiveSheet.Paste
 Range("C4").Select
 Application.CutCopyMode = False
 ActiveCell.FormulaR1C1 = "0003"
 Range("D3").Select
 Selection.Copy
 Range("D4").Select
 ActiveSheet.Paste
 Range("E4").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -2).Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("DETALLE").Select
 ActiveSheet.Paste
 Range("F3").Select
 Application.CutCopyMode = False
 Selection.Copy
 Range("F4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("G4").Select
 ActiveCell.FormulaR1C1 = "D"
 Range("H3").Select
 Selection.Copy
 Range("H4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("I3").Select
 Selection.Copy
 Range("I4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("J3").Select
 Selection.Copy
 Range("J4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("K3").Select
 Selection.Copy
 Range("K4").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("M4").Select
 ActiveCell.FormulaR1C1 = " "
 Range("N4").Select
 ActiveCell.FormulaR1C1 = "S"
 Range("O3").Select
 Selection.Copy
 Range("O4").Select
 ActiveSheet.Paste
 Rows("2:2").Select
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Range("A2").Select
 Sheets("CABECERA").Select
 Rows("2:2").Select
 Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 Range("A2").Select
 Sheets("DATOS").Select
 ActiveCell.Offset(0, -7).Select
 ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sustituí "Select
ActiveSheet.Paste"

Por "Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False"

Y salió error "438"

Una vez mas muchas gracias

Dios le bendiga

El "Pegado como Valores" (la linea de código que te pase) realízalo en la linea posterior.

Range("E2").Select
ActiveCell.FormulaR1C1 = "F"
Range("F2").Select
ActiveCell.FormulaR1C1 = "0"
Range("G2").Select
Sheets("DATOS").Select
ActiveCell.Offset(0, 1).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CABECERA").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False
Range("H2").Select
Sheets("DATOS").Select
ActiveCell.Offset(0, -3).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CABECERA").Select
ActiveSheet.Paste
Range("I2").Select
ActiveCell.FormulaR1C1 = "V"
Range("J2").Select
ActiveCell.FormulaR1C1 = "S"

Funciono!!!!!

Un pequeño detalle la información sale centrada como puedo hacer para que salga hacia el lado izquierdo de la celda.

Muchas gracias

Que Dios bendiga siempre su vida.

Espero estés de maravilla!

Me alegra mucho saber que has solucionado una parte de tu problema.

Para poder poner el texto sobre la izquierda debes darle la siguiente instrucción a la celda activa o bien al grupo de celdas en donde lo quieras aplicar.

With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Bendiciones!

Lucas.

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas