collegamenti persi ref

Hai un problema con Excel?

Nuova discussione

collegamenti persi (#ref)
di roberto21 data: 12/01/2015 18:17:01
Ciao a tutti. Cerco di semplificare al massimo il problemino. Ho un workbook con tre fogli. Foglio1 è riempito quotidianamente; ogni tanto, viene lanciata una macro che spazza i record su foglio1 e ne riporta alcuni, con qualche elaborazione, su foglio2. La macro all'inizio crea una copia di foglio2 (che si chiamerà foglio2 (2)) e lavora su questa copia. Alla fine, se tutto va bene, foglio2 viene eliminato e foglio2 (2) viene rinominato a foglio2.
Il problemino è che foglio3 ha qualche cella che fa riferimento a celle di foglio2 (...= 'foglio2'!A5 e simili) e alla fine della macro questi riferimenti sono saltati (#ref). E' vero che ho cancellato foglio2, ma poi ho rinominato foglio2 (2) a foglio2, quindi pensavo (erroneamente, pare) che al riferimento bastasse il nome del foglio. Dove è che sbaglio? Grazie a tutti.
di lepat (utente non iscritto) data: 13/01/2015 07:42:15
allega un file di esempio per testare il tuo codice

di roberto21 data: 13/01/2015 10:21:23
Nessun problema, vi allego tutta la macro, non è niente di particolare (poi non è sicuramente scritta bene, ma sto imparando). Rispetto alla spiegazione iniziale, "Banca 2015" è foglio2 ( e viene copiata in "Banca 2015 (2)"), "Prima nota 2015" è foglio1. La parte di elaborazione funziona, nel senso che quello che deve apparire in "Banca 2015 (2)" è OK.
Tenete conto che ho fatto una prova come segue: copio "Banca 2015" per sicurezza, ma lavoro sull'originale ("Banca 2015") invece che su "Banca 2015 (2)". Alla fine cancello "Banca 2015 (2)". In questo caso, i riferimenti rimangono OK, quincdi sembrerebbe che proprio la cancellazione di "Banca 2015" nella prima versione sia la causa del problema, e il rename non abbia effetto. Grazie

Sub Macro1()
'
' Macro1 Macro
'
Dim LastRow, I, Index As Long

'create a copy of Banca
    Sheets("Banca 2015").Select
    Application.DisplayAlerts = False
    Sheets("Banca 2015").Copy Before:=Sheets(3)
    Application.DisplayAlerts = True
    Index = 3

' clear all rows except 1 and 2
With Worksheets("Banca 2015 (2)")
    Rows("3:" & .Rows.Count).Delete
End With
Sheets("Prima nota 2015").Activate
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
          
    For I = 3 To LastRow
      If CSng(Cells(I, 1)) < CSng("00101") Then
        
        Worksheets("Banca 2015 (2)").Cells(Index, 1) = Cells(I, 1)
        Range("B" & I).Select
        Selection.Copy
        Sheets("Banca 2015 (2)").Activate
        Range("B" & Index).Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Sheets("Prima nota 2015").Activate
        
        Worksheets("Banca 2015 (2)").Cells(Index, 3) = Cells(I, 3)
        If CSng(Cells(I, 1)) = CSng("00025") Then
            Worksheets("Banca 2015 (2)").Cells(Index, 4) = Cells(I, 5)
        Else
            Worksheets("Banca 2015 (2)").Cells(Index, 5) = Cells(I, 4)
        End If
        Worksheets("Banca 2015 (2)").Cells(Index, 6) = Cells(I, 6)
        
        Sheets("Banca 2015 (2)").Select
        Sheets("Banca 2015 (2)").Range("G2:L2").Select
        Selection.Copy
        Worksheets("Banca 2015 (2)").Cells(Index, 7).Select
        Worksheets("Banca 2015 (2)").Paste
        
        Sheets("Prima nota 2015").Select
        ' se codice 00025 POS aggiungere una riga di commissioni POS
        ' (codice 0002) come USCITE
        
        If CSng(Cells(I, 1)) = CSng("00025") Then
            Index = Index + 1
            Worksheets("Banca 2015 (2)").Cells(Index, 1) = "00026"
            Range("B" & I).Select
            Selection.Copy
            Sheets("Banca 2015 (2)").Activate
            Range("B" & Index).Select
            Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            Sheets("Prima nota 2015").Activate
            
            
      
            Worksheets("Banca 2015 (2)").Cells(Index, 3) = Cells(I, 3)
            Worksheets("Banca 2015 (2)").Cells(Index, 5) = Cells(I, 5) * 0.7 / 100
            Worksheets("Banca 2015 (2)").Cells(Index, 6) = "Commissione Bancomat"
            
            Sheets("Banca 2015 (2)").Select
            Sheets("Banca 2015 (2)").Range("G2:L2").Select
            Selection.Copy
            Worksheets("Banca 2015 (2)").Cells(Index, 7).Select
            Worksheets("Banca 2015 (2)").Paste
        End If
        
        Sheets("Prima nota 2015").Select
        Index = Index + 1
    End If
    Next I
' delete old sheet and rename new one
    
    Application.DisplayAlerts = False
    Sheets("Banca 2015").Delete
    Application.DisplayAlerts = True
    
    Sheets("Banca 2015 (2)").Name = "Banca 2015"

End Sub

di Vecchio Frac data: 13/01/2015 11:09:13
Bè non è strano, quando cancelli un foglio ne elimini i riferimenti.
Quando lo rinomini, ne cambi solo il nome "esterno" mentre il codename (interno) rimane uguale.
Ogni foglio nasce con un codename interno non accessibile e non manipolabile (bè, non è vero ^_^) che coincide con foglio1, foglio2, foglio3 ecc.

Per rifarci al primo post, quindi, all'inizio i tuoi fogli "foglio1" e "foglio2" hanno come codename "foglio1" e "foglio2", e se copi il foglio2 viene creato un altro foglio il cui nome interno sarà per esempio "foglio3" ma il nome esterno sarà "foglio2 (2)". Se elimini il foglio che ha nome esterno "foglio2", il codename "foglio2" non esisterà più e tutti i suoi riferimenti saranno persi.

La situazione è chiara se apri l'editor di codice e apri la finestra progetti con Ctrl-R, dove puoi vedere i nomi esterni dei fogli (quelli sulle linguette per capirci) e quelli interni (i famosi codename).

Per questo motivo se lavori sull'originale non avrai problemi: i riferimenti rimangono intatti.

Si può provare a studiare un qualcosa (accedere al codename si può) ma se riesci a lavorare così allora va bene..
di Roberto21 (utente non iscritto) data: 13/01/2015 12:02:57
Grazie mille. Ho imparato qualcosza di nuovo (nome "interno" del foglio"). Magari ci studio un po' su...

di Vecchio Frac data: 13/01/2015 13:01:32
Un po' di test alla tua attenzione.

Option Explicit

Sub test()

' prendo il foglio1 e lo rinomino quindi lo ricopio e ne ottengo un doppione
' se apro la finestra progetti (Alt-F11 e Ctrl-R) noto la differenza tra nome esterno e nome interno

    Sheets("Foglio1").Name = "Banca 2015"
    
    Sheets("Banca 2015").Copy after:=Sheets("Banca 2015")
    
End Sub


Sub test2()

' rinomino il foglio1 e lo copio creando un duplicato
' i codename interni sono diversi (per quello che era il foglio1 però rimane foglio1)

    Sheets("Foglio1").Name = "Banca 2015"
    Sheets("Banca 2015").Copy after:=Sheets("Banca 2015")
    
' elimino l'originale foglio1 che avevo rinominato... ne elimino anche il codename originale che era sempre foglio1
    Application.DisplayAlerts = False
    Sheets("Banca 2015").Delete

'rinomino il doppione chiamandolo col nome del "padre"... ma il suo codename rimane intatto e NON è foglio1 (che è sparito)
    Sheets("Banca 2015 (2)").Name = "Banca 2015"
    
' ora con uno sporco trucco riassegno il codename foglio1, che era l'originale, al foglio figlio rinominato
    ThisWorkbook.VBProject.VBComponents(Sheets("Banca 2015").CodeName).Properties("_CodeName") = "Foglio1"
    
    Application.DisplayAlerts = True

' purtroppo questo trucco non conserva i riferimenti al foglio precedentemente impostati nelle formule
' le celle che contenevano riferimenti al vecchio foglio1 eliminato, ricevono l'errore RIF

End Sub

di Roberto21 (utente non iscritto) data: 13/01/2015 15:42:31
E' vero, c'ero arrivato anch'io...pensavo di essere furbo e invece... il codice che avevo scritto (ometto la parte centrale, che non c'entra e comunque funziona) lo allego qui... Ma allora dobbiamo concludere che i riferimenti saltano anche quando sia il nome che il codename non cambiano?

Sub Macro1()
'
' Macro1 Macro
'
Dim LastRow, I, Index As Long
Dim ActualCodename, ActualCodename2 As String

'create a copy of Banca
    Sheets("Banca 2015").Select
' get the codename for "Banca 2015" to be used later...
    ActualCodename = Sheets("Banca 2015").CodeName
    Application.DisplayAlerts = False
    Sheets("Banca 2015").Copy Before:=Sheets(3)
    
    Application.DisplayAlerts = True
    Index = 3
    
'working code follows

'.........................................

' delete old sheet and rename new one
    
    Application.DisplayAlerts = False
    Sheets("Banca 2015").Delete
    Application.DisplayAlerts = True
' rename new worksheet... (external name)
    Sheets("Banca 2015 (2)").Name = "Banca 2015"
'get codename of this new worksheet...
    ActualCodename2 = Sheets("Banca 2015 (2)").CodeName
' restore original codename...
    With ActiveWorkbook
    .VBProject.VBComponents(ActualCodename2).Properties("_CodeName").Value = ActualCodename
    End With
    
End Sub

di roberto21 (utente non iscritto) data: 13/01/2015 15:49:19
Scusa, c'e un errore verso la fine, questo è il codice giusto, ma comunque i riferimenti saltano anche se il codename è corretto.

Sub Macro1()
'
' Macro1 Macro
'
Dim LastRow, I, Index As Long
Dim ActualCodename, ActualCodename2 As String

'create a copy of Banca
    Sheets("Banca 2015").Select
' get the codename for "Banca 2015" to be used later
    ActualCodename = Sheets("Banca 2015").CodeName
    Application.DisplayAlerts = False
    Sheets("Banca 2015").Copy Before:=Sheets(3)
   
    Application.DisplayAlerts = True
   
' working code follows...

'***************************************+


' delete old sheet and rename new one
    
    Application.DisplayAlerts = False
    Sheets("Banca 2015").Delete
    Application.DisplayAlerts = True
 
'Rename sheet (EXTERNAL name)   
    Sheets("Banca 2015 (2)").Name = "Banca 2015"
'get codename of this renamed sheet
    ActualCodename2 = Sheets("Banca 2015").CodeName
' restore original codename saved at the beginning...
    With ActiveWorkbook
    .VBProject.VBComponents(ActualCodename2).Properties("_CodeName").Value = ActualCodename
    End With
    
End Sub

Vuoi Approfondire?