Hojas Ocultas y UserForm

Te recomiendo mejor expongas el código para saber
exactamente que hace y cual sería la modificación.

A sus órdenes.

Hola:

Quisiera saber si me pueden ayudar con lo siguiente:
Tengo un libro que tiene varias hojas y además varios
macros.
Quisiera poder crear un Userform para poder correr todas
las macros desde allí y tener ocultas todas las hojas.
He probado ocultando alguna hoja y ejecutando un macro
pero me devuelve un error pues imagino que no encuentra

la

hoja.
¿Es posible que el macro funcione si las hojas que
utilizan están ocultas?
¿Qué debería hacer?

Agradezco desde ya cualquier ayuda.

Saludos,
Marcelo
.

Aquí copio uno de los códigos.
La idea es poder ejecutarlo si las hojas están ocultas pero que también
funcione si las hojas está visibles.


Gracias Nuevamente y Aguardo comentarios

Saludos

Marcelo




Sub generar_descargas()
'
' generar_descargas
' Macro grabada el 26/3/04 por mpaz
'

'
'Aquí se agrega una nueva hoja y se definen las propiedades de las celdas

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Descarga").Delete
Worksheets("Despachante").Delete
Application.DisplayAlerts = True

Sheets(1).Select
Sheets.Add
Sheets(1).Select
ActiveSheet.Name = "Descarga"
Sheets("DESCARGA").Select
Cells.Select
With Selection.Font
.Name = "Tahoma"
.Size = 8
.Strikethrough = False

With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With

Range("A2:A3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With

Range("B2:B3").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom

End With

Range("A2:B3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With


Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("A2") = "EXPORT Nº"
Range("A3") = "FACTURA"
Range("B2") = "=DOCUMENTOS!CK9"
Range("B3") = "=DOCUMENTOS!CM2"

End With

Sheets("A.T.").Select
Range("A2:A1000,B2:B1000,C2:C1000,P2:P1000,Q2:Q1000").Select
Selection.Copy
Sheets("DESCARGA").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' Elimina filas de A.T. Canceladas

With Worksheets("DESCARGA")
Range("E8:E1000").Replace "CANCELADA", "", xlWhole
Range("E8:E1000").Replace "CANCELAR", "", xlWhole
Range("E8:E1000").Replace "TRÁMITE", "", xlWhole
Range("E8:E1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

' Elimina Columna con estado de A.T.

Columns("E:E").Select
Selection.Delete Shift:=xlToLeft

Rows("8:30").Select
Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Key2:=Range("A8")
_
, Order2:=xlAscending, Header:=xlGuess

' Se Seleccionan y copian los nombres de los modelos

Sheets("CONSUMOS").Select
ActiveWindow.SmallScroll ToRight:=-18
Range("D2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DESCARGA").Select
Range("L5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

' Aquí se calculan las unidades por modelo y se eliminan los modelos
' con valor 0

Range("L6").Select
ActiveCell.FormulaR1C1 = _

"=SUMIF(DOCUMENTOS!R31C126:R50C126,R[-1]C,DOCUMENTOS!R31C127:R50C127)"
Range("L6").Select
Selection.Copy
Range("L5").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(1, 0).Select
Range(Selection, Selection.End(xlToLeft).Offset(0, 1)).Select
ActiveSheet.Paste
Range("L6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=-23
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("L8").Select
Selection.FormulaR1C1 = _

"=IF(RC1>0,INT(VLOOKUP(RC2,CONSUMOS!R6C1:R50C54,COLUMN()-8,FALSE)*R6C))"
Range("L8").Select
Selection.Copy
Range("L6").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(24, 0).Select
Range(Selection, Selection.End(xlUp).Offset(2, 0)).Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveSheet.Paste
Range("8:30").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G8").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[5]:RC[47])"
Range("G8").Select
Selection.Copy
Range("G9:G30").Select
ActiveSheet.Paste
Range("G8:G30").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = Fals


Range("F8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]>0,""SI"",""NO"")"
Range("F9").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-4]="""",""NO"",IF(RC[-4]<>R[-1]C[-4],""SI"",IF(AND(RC[-4]=R[-1]C[-4
],R[-1]C[-2]>R[-1]C[1]),""NO"",""SI"")))"
Range("F9").Select
Selection.AutoFill Destination:=Range("F9:F30"), Type:=xlFillDefault
Range("F8:F30").Select
Selection.Copy
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("E8:E30").Replace "NO", "", xlWhole
Range("E8:E30").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Range("L6:BB6").Replace "0", "", xlWhole
Range("L6:BB6").SpecialCells(xlCellTypeBlanks).EntireColumn.Delete

Columns("F:F").Select
Selection.Delete Shift:=xlToLeft

Range("G8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]>RC[-1],RC[-1],RC[-3])"
Range("H8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"

Range("G9").Select
ActiveCell.FormulaR1C1 "=SUMIF(R7C[-5]:R[-1]C[-5],RC[-5],R7C[-3]:R[-1]C[-3])"
Range("H9").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-6]<>R[-1]C[-6],IF(RC[-4]>RC[-2],RC[-2],RC[-4]),IF(AND(R[-1]C[-6]=RC
[-6],R[-1]C[-4]>=RC[-2]),0,IF(AND(R[-1]C[-6]=RC[-6],RC[-4]+R[-1]C[-1]<RC[-2]
),RC[-4],IF(AND(R[-1]C[-6]=RC[-6],RC[-4]+R[-1]C[-1]>RC[-2]),RC[-2]-SUMIF(R7C
[-6]:R[-1]C[-6],RC[-6],R7C:R[-1]C)))))"
Range("G9:H9").Select
Selection.Copy
Range("G10:H30").Select
ActiveSheet.Paste
Range("8:30").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("H:H").Replace "0", "", xlWhole
Range("H8:H30").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Rows("8:30").Select
Selection.Copy
Range("A31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K31").Select
ActiveCell.FormulaR1C1 = _

"=IF(R[-23]C=0,0,IF(R[-23]C>0,IF(RC6<=RC8,R[-23]C,IF(RC6>=RC8,(INT(R[-23]C*(
RC8/RC6))+1),0))))"
Range("K31").Select
Selection.Copy
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Rows("31:53").Select
Selection.Copy
Range("A31").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:_
False, Transpose:=False

Rows("8:30").Select
Selection.Delete Shift:=xlUp
Columns("F:F").Select
Selection.Copy
Columns("J:J").Select
ActiveSheet.Paste
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]>0,VLOOKUP(RC[-3],A.T.!C[-3]:C[14],13,FALSE),"""")"
Range("E8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]>0,SUM(RC[6]:RC[49]),"""")"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]>0,RC[-2]*RC[-1],"""")"
Range("D8:F8").Select
Selection.Copy
Range("D9:F30").Select
ActiveSheet.Paste
Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Selection.NumberFormat = "0.0000"
Columns("E:E").Select
Selection.NumberFormat = "#,##0"
Columns("F:F").Select
Selection.NumberFormat = "#,##0.00"
Range("A6").Select
ActiveCell.FormulaR1C1 = "A.T. N°"
Range("B6").Select
ActiveCell.FormulaR1C1 = "MATERIA PRIMA"
Range("C6").Select
ActiveCell.FormulaR1C1 = "NCM"
Range("D6").Select
ActiveCell.FormulaR1C1 = "USD/UN"
Range("E6").Select
ActiveCell.FormulaR1C1 = "CANTIDAD"
Range("F6").Select
ActiveCell.FormulaR1C1 = "USD TOTAL"
Columns("G:H").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("A6:F6").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:G").ColumnWidth = 1.71

Range("I31").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-26]C,Productos!C3:C23,11,FALSE)"
Selection.Copy
Range("I6").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(25, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
ActiveSheet.Paste


Sheets(1).Select
Sheets.Add
Sheets(1).Select
ActiveSheet.Name = "Despachante"
Sheets("DESPACHANTE").Select
Range("A1") = "A.T."
Range("B1") = "EXPORT"
Range("C1") = "UN EXPORTADAS"
Range("D1") = "MODELO"
Range("E1") = "CANTIDAD DESCARGADA"
Range("F1") = "UNIDADES TOTAL"
Range("G1") = "TOTAL"
Range("H1") = "USD TOTAL"
Range("I1") = "USD"
Range("J1") = "C.TOTAL"
Range("K1") = "NCM"
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter


Dim Fila As Integer
For Fila = 8 To 30
If Application.Sum(Worksheets("DESCARGA").Range("A" & Fila)) = 0 Then
Exit For

Sheets("Descarga").Select
Range("I" & Fila).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("E65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("Descarga").Select
Range("I5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("D65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Sheets("Descarga").Select
Range("I6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("F65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("Descarga").Select
Range("I31").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("K65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:_
False, Transpose:=True

Sheets("Descarga").Select
Range("A" & Fila).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("E65536").End(xlUp).Offset(0, -4).Select
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("DESPACHANTE").Select
Range("A65536").End(xlUp).Select
Selection.Delete
Sheets("Descarga").Select
Range("H" & Fila).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("E65536").End(xlUp).Offset(0, 2).Select
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("DESPACHANTE").Select
Range("G65536").End(xlUp).Select
Selection.Delete

Sheets("Descarga").Select
Range("E" & Fila).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("E65536").End(xlUp).Offset(0, 5).Select
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("DESPACHANTE").Select
Range("J65536").End(xlUp).Select
Selection.Delete

Sheets("Descarga").Select
Range("F" & Fila).Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("E65536").End(xlUp).Offset(0, 3).Select
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("DESPACHANTE").Select
Range("H65536").End(xlUp).Select
Selection.Delete

Next
Application.CutCopyMode = False

Sheets("Descarga").Select
Range("B2").Select
Selection.Copy
Sheets("DESPACHANTE").Select
Range("E65536").End(xlUp).Offset(0, -3).Select
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("DESPACHANTE").Select
Range("B65536").End(xlUp).Select
Selection.Delete

Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[7]*RC[3]/RC[4]"
Range("C2").Select
Selection.Copy
Range("E65536").End(xlUp).Offset(0, -2).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]*RC[-1]/RC[-2]"
Range("I2").Select
Selection.Copy
Range("E65536").End(xlUp).Offset(0, 4).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

Cells.Select
With Selection.Font
.Name = "Tahoma"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Tahoma"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C:C,E:G").Select
Range("E1").Activate
Selection.NumberFormat = "#,##0"
Columns("H:I").Select
Selection.NumberFormat = "#,##0.00"
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Columns("F:H").Select
Selection.Delete Shift:=xlToLeft
Columns("G").Select
Selection.Delete Shift:=xlToLeft

Range("E:E").Replace "0", "", xlWhole
Range("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:G").Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets("Descarga").Select
Columns("H").Select
Selection.Delete Shift:=xlToLeft
Rows("31").Select
Selection.Delete Shift:=xlUp

End With

End With

End Sub










"Sergio A Campos H" escribió en el mensaje
news:1c63301c4528e$781c22c0$
Te recomiendo mejor expongas el código para saber
exactamente que hace y cual sería la modificación.

A sus órdenes.

Hola:

Quisiera saber si me pueden ayudar con lo siguiente:
Tengo un libro que tiene varias hojas y además varios
macros.
Quisiera poder crear un Userform para poder correr todas
las macros desde allí y tener ocultas todas las hojas.
He probado ocultando alguna hoja y ejecutando un macro
pero me devuelve un error pues imagino que no encuentra

la

hoja.
¿Es posible que el macro funcione si las hojas que
utilizan están ocultas?
¿Qué debería hacer?

Agradezco desde ya cualquier ayuda.

Saludos,
Marcelo
.