tengo problema con esta macro

[exorton]

Tengo un excel con muchas hojas, en cada hoja me interesa una tabla que aparece y que tiene diferente cantidad de columnas y filas, pero todas comienzan en la "D9" de cada hoja.

Me interesa hacer un listado de el contenido de la tabla y el nombre de la hoja, lo hise de la siguiente manera.

Código vb:

hojas = Application.Sheets.Count - 4
    b = 1
    a = 0
        
    For i = 1 To hojas
        
        Sheets(i).Select
        Range("D9").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        
        Rw = Selection.Rows.Count
        
        Sheets("Hoja4").Select
        Cells(b, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        b = b + a
                
        For a = 0 To Rw
            Worksheets("Hoja4").Cells(b, 0).Value = Sheets(i).Name
        Next a
 
    Next i
 

me da error 1004 en la Parte donde dice Cells(b,1).select en la linea 16

Alguna idea??, al programar solo pegar sin el resto del código no tengo problemas.

[Monimo]

Prueba con poner Sheets("Hoja4").cells(b,1).select

[exorton]

si probe y con Worksheets("Hoja4") pero mismo error

[exorton]

Hise una modificacion, arreglando unos errores de logica y me cambio el error ahora es en la linea 24

Sheets("Hoja4").Cells(b, 1).Select

El error es por subindice fuera del intervalo (tipo 9)

La verdad que no veo nada de eso, de hecho aumente los valores

Código vb:

hojas = Application.Sheets.Count - 4
    b = 1
    a = 0
    Dim Ficha As Integer
        
    For i = 1 To hojas
        
        Ficha = Sheets(i).Name
        Sheets(i).Select
        Range("D9").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        
        Rw = Selection.Rows.Count
        
        Sheets("Hoja4").Select
        Cells(b, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
               
        For a = 1 To Rw
            b = b + a
            Sheets("Hoja4").Cells(b, 1).Select
        Next a
 
    Next i
 

[mrocf]

Hola! exorton.
El primer código mostrado -probablemente- lo tuvieses en el módulo de una hoja que NO era Hoja4, y de allí el error.

De todas formas ibas a tener problemas con:

Worksheets("Hoja4").Cells(b, 0)

pues no existe la fila "0".
_______________

El segundo código también es problemático en varias partes. Por ejemplo:

Dim Ficha As Integer (defines Ficha como un número entero)
Ficha = Sheets(i).Name (pero lo usas como un String)

Además no escribes en la columna A el nombre de la hoja de extracción.
_______________

Analiza esos detalles y analiza -también- una alternativa como:

Código PHP:

  Sub ListadoDeTablas()
Dim i As Integer, actRow As Long
 
Application.ScreenUpdating = False
 
For i = 1 To Sheets.Count - 4
  With Sheets(i)
    With .Range(.[d9], .Cells(.[d9].End(xlDown).Row, .[d9].End(xlToRight).Column))
      actRow = 1 + Sheets("Hoja4").Cells(Rows.Count, "B").End(xlUp).Row
      .Copy Sheets("Hoja4").Cells(actRow, "B")
      Sheets("Hoja4").Cells(actRow, "A").Resize(.Rows.Count) = Sheets(i).Name
    End With
  End With
Next
 
Application.ScreenUpdating = True
End Sub 
   

Este código -lo ubiques donde lo ubiques- debería funcionar adecuadamente.

¿Comentas, por favor?
Saludos, Cacho R.

[exorton]

@mrocf Sos grande!!! Me funciono, pero necesito dos cosas mas.

1- Que tome desde la columna D asta la I, el .[d9].End(xlToRight).Column funciona con algunas hojas no con todas y solo necesito esas columanas, en las filas esta OK.

2.- que copie solo valores

usaste una programación que yo no conosia sabes de que tipo es?? donde puedo obtener mas info sobre esa manera de programar los macros??. Pero funciona muy bien.

[exorton]

ya lo hice a mi manera.

Código vb:

Application.ScreenUpdating = False
hojas = Application.Sheets.Count - 5
 
Sheets("Hoja4").Range("B2").Select
 
For i = 1 To hojas
    Sheets(i).Select
    Range("D9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.Cells(1, 7)).Select
    Selection.Copy
    
    Rw = Selection.Rows.Count
    
    Sheets("Hoja4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    For a = 1 To Rw
        Selection.End(xlDown).Offset(a - Rw, -1).Value = Sheets(i).Name
    Next a
    
    Selection.End(xlDown).Offset(1, 0).Select
Next i
 
Application.ScreenUpdating = True
 

}

Tiene un tiempo de ejecución mucho mas rápido (son 216 hojas), en como 5 segundos entrega el resultado y creo que esta todo bien.

Gracias por el apoyo y @mrocf favor explícame tu código entiendo parte pero no todo y me gustaría aprender.