me pone error 9 al tiempo de ejecucion

2 envíos / 0 nuevos
Último envío
tetorin
Imagen de tetorin
Offline
última acción: Hace 7 meses 1 semana
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntos
alta: 26/05/2011 - 02:57
Puntos: 3262
me pone error 9 al tiempo de ejecucion

buen dia Ojala y me puedan ayudar me pone Error 9
   

  Sheets("Sheet2").Visible = False

 

anexo codigo

 

Private Sub Save_PlacardTag_Click()
Dim in_path, file_name, pcard(8), st, ut As String
Dim new_name, k_name As Variant
Dim ws As Worksheet
Dim i, j, k, l, ino, lng As Integer
' Save as an existing file             k=1
' Save as a newly created file         k=2
k = 1
file_name = ActiveWorkbook.Name
Sheets("D").Range("AJ16").Value = Sheets("BG").Cells(12, "V").Value
k_name = Sheets("D").Range("AJ16").Value & ".xls"
in_path = ActiveWorkbook.Path
ChDir in_path
new_name = Application.GetSaveAsFilename(k_name, "Excel files, *.xls")
If (new_name = False) Then
    Exit Sub
Else
    Sheets("D").Range("AJ16").Value = new_name
End If

On Error GoTo 100
Workbooks.Open Filename:=new_name
GoTo 200

100 Workbooks.Add
    k = 2
    Sheets("Sheet2").Visible = False
    Sheets("Sheet3").Visible = False
    ActiveWorkbook.SaveAs Filename:=new_name, _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False

200 l = Len(new_name)
i = 5
Do
    If (i > 500) Then
        MsgBox ("Unreasonable Output file name detected.")
        Exit Sub
    End If
    st = Right(new_name, i)
    ut = Left(st, 1)
    If (ut = "\") Then
        Exit Do
    End If
    i = i + 1
Loop
st = Right(new_name, i - 1)

On Error GoTo 0
If (k = 1) Then
    MsgBox ("All existing sheets in " & st & " file will be prefixedly renamed.")
    Windows(st).Activate
    ino = 0
    For Each ws In Worksheets
        ino = ino + 1
    Next ws
    For i = ino To 1 Step -1
        If (Left(Sheets(i).Name, 1) = "0" Or Left(Sheets(i).Name, 1) = "1") Then
            j = Int(Left(Sheets(i).Name, 2)) + 1
            lng = Len(Sheets(i).Name)
            If (j > 9) Then
                Sheets(i).Name = CStr(j) & Right(Sheets(i).Name, lng - 2)
            Else
                Sheets(i).Name = "0" & CStr(j) & Right(Sheets(i).Name, lng - 2)
            End If
        Else
            Sheets(i).Name = "01-" & Sheets(i).Name
        End If
    Next i
End If

j = 1
Windows(file_name).Activate
For i = 8 To 1 Step -1
    If (i = 1) Then
        Windows(file_name).Activate
        Sheets("Placard(1) 11x17").Select
        Sheets("Placard(1) 11x17").Copy before:=Workbooks(st).Sheets(j)
        Windows(file_name).Activate
        Sheets("Placard(1) 11x17").Range("D27:L27").Select
        Selection.Copy
        Windows(st).Activate
        Sheets("Placard(1) 11x17").Range("D27:L27").Select
        ActiveSheet.Paste
        j = j + 1
        Windows(file_name).Activate
        Sheets("Placard(1) 11x17 Port.").Select
        Sheets("Placard(1) 11x17 Port.").Copy before:=Workbooks(st).Sheets(j)
        Windows(file_name).Activate
        Sheets("Placard(1) 11x17 Port.").Range("C25:K25").Select
        Selection.Copy
        Windows(st).Activate
        Sheets("Placard(1) 11x17 Port.").Range("C25:K25").Select
        ActiveSheet.Paste
        j = j + 1
        Windows(file_name).Activate
    End If
    pcard(i) = "Placard(" & CStr(i) & ")"
    For Each ws In Worksheets
        If (ws.Name = pcard(i)) Then
            Windows(file_name).Activate
            Sheets(pcard(i)).Select
            Sheets(pcard(i)).Copy before:=Workbooks(st).Sheets(1)
            Windows(file_name).Activate
            Sheets(pcard(i)).Range("C25:K25").Select
            Selection.Copy
            Windows(st).Activate
            Sheets(pcard(i)).Range("C25:K25").Select
            ActiveSheet.Paste
            Windows(file_name).Activate
            j = j + 1
        End If
    Next ws
Next i
Windows(file_name).Activate
Sheets("Lock Tags").Select
Sheets("Lock Tags").Copy before:=Workbooks(st).Sheets(j)
Windows(file_name).Activate
Sheets("Sign").Select
Sheets("Sign").Copy after:=Workbooks(st).Sheets(1)
Sheets("Placard(1)").Select
ActiveWorkbook.Save
ActiveWindow.Close
Sheets("D").Visible = False
Sheets("BG").Visible = False
Sheets("Main").Select
MsgBox (st & " has been saved.")
End Sub

Etiquetas: 

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 6 horas 58 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 11175
Re: me pone error 9 al tiempo de ejecucion

el error 9 es "subíndice fuera de intervalo"; ocurre por ejemplo cuando llamas a la hoja 7 de un libro que sólo tiene 3 hojas.

En este caso, en que se produce con 

Sheets("Sheet2").Visible = False

comprueba que tienes una hoja que se llama Sheet2

 

------
Ya sé Excel, pero necesito más.