Excel - Copiar una Macro en otras tres Hojas

   
Vista:

Copiar una Macro en otras tres Hojas

Publicado por Jorge Cervantes (35 intervenciones) el 15/03/2013 00:44:17
Tengo Cuatros Hojas, Semana1,semana2,semana3,semana4. He generado en la hoja semana1 mediante la grabadora de macros lo siguiente y lo que necesito es una macro que haga los mismo utilizando la misma macro para las otras hojas. Puedo hacerlo grabando otra macro y copiando las formulas formatos de cada hoja y tendría el mismo resultado para todas. Yo creo que hay otro método mas sencillo.
Gracias
Sub Desarrollo()
'
' Desarrollo Macro
' Hecho por el generador de Macros

'
Columns("B:E").Select
Range("B2").Activate
Selection.ColumnWidth = 14
Range("B10").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Range("B10").Select
Selection.AutoFill Destination:=Range("B10:E10"), Type:=xlFillDefault
Range("B10:E10").Select
Range("B11").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-8]C:R[-2]C)"
Range("B11").Select
Selection.AutoFill Destination:=Range("B11:E11"), Type:=xlFillDefault
Range("B11:E11").Select
Range("D9:E9").Select
Selection.Copy
Range("D10:E11").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B1:C1,D1:F1,A14:B14").Select
Range("A14").Activate
Selection.Style = "Énfasis1"
Range("B2:F2").Select
Selection.Style = "Entrada"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F11"), Type:=xlFillDefault
Range("F3:F11").Select
Range("A3:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A14:B16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B1:F2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
Range("A3:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A3:F11").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
Range("A14:B16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlDouble
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick
End With
Range("F14").Select
ActiveCell.FormulaR1C1 = "cervantes"
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Jorge Cervantes\Documents\Productos.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Range("D12").Select
End Sub
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Copiar una Macro en otras tres Hojas

Publicado por Jorge Cervantes (35 intervenciones) el 15/03/2013 01:15:48
Ya encontré la solución

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub modificar()
 
If opcion = semana2 Then
   Sheets("semana2").Activate
   Call Desarrollo
End If
 
If opcion = semana3 Then
   Sheets("semana3").Activate
   Call Desarrollo
End If
 
If opcion = semana4 Then
    Sheets("semana4").Activate
    Call Desarrollo
End If
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Copiar una Macro en otras tres Hojas

Publicado por Armando Montes (240 intervenciones) el 15/03/2013 04:02:48
Puedes eliminar select y reducir el codigo para borders con algo asi:
1
Range("A3:F11").BorderAround Weight:=xlThick


Otro ejemplo:
1
2
3
4
5
6
With Sheet1.Range("B8:I10")
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
End With


Mas info:
http://msdn.microsoft.com/en-us/library/office/aa141062(v=office.10).aspx
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Copiar una Macro en otras tres Hojas

Publicado por Armando Montes (240 intervenciones) el 15/03/2013 04:40:05
Otro:
1
2
3
Sub Add_Border()
Range("A1:D4").Borders.LineStyle = xlContinuous
End Sub
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar