Ofimática - CELDAS DEPENDIENTES

Option Explicit

'//By JuanC - Set. 2011

'//Muestra las celdas dependientes de cada celda para cada hoja del libro

Sub Ver_Dependencias()

Dim rngPrec As Range, cell As Range

Dim ws As Worksheet, c As Range

Dim wbkDst As Workbook, wbkSrc As Workbook, lRow&, lCol&

On Error Resume Next

lRow = 1

Application.ScreenUpdating = False

Set wbkSrc = ActiveWorkbook   '//Trabaja sobre las celdas del libro activo...

Set wbkDst = Workbooks.Add    '//Agrega libro para guardar resultados...

For Each ws In wbkSrc.Sheets  '//Recorre todas las hojas del libro...

    For Each cell In ws.UsedRange  '//Toma rango usado...

        If Not cell Is Nothing Then

           If cell <> "" Then

              Set rngPrec = cell.Precedents

              If Not rngPrec Is Nothing Then '//Si la celda tiene precedentes...

                 wbkDst.Sheets(1).Range("A" & lRow) = ws.Name  '//Guarda nombre de hoja

                 wbkDst.Sheets(1).Range("B" & lRow) = cell.Address(0, 0) '//y celda...

                 lCol = 3

                 For Each c In rngPrec '//Guarda dirección de las celdas dependientes...

                     wbkDst.Sheets(1).Cells(lRow, lCol) = c.Address

                     lCol = lCol + 1

                 Next

                 lRow = lRow + 1

              End If

              Set rngPrec = Nothing

           End If

        End If

    Next

Next

Set cell = Nothing

Set c = Nothing

Set wbkSrc = Nothing

Set wbkDst = Nothing

Set ws = Nothing

Application.ScreenUpdating = True

End Sub