Macro raccogli dati da vari fogli

[Belvel]

Ciao a tutti

allego il foglio "prova".

http://www.filedropper.com/prova_24

In tale foglio ho 6 fogli che ho chiamato Foglio1, Foglio2, ecc.. più un settimo foglio chiamato "indice".
I fogli da 1 a 4 inclusi hanno un dato nella cella O2, mentre i fogli 5 e 6 ce l'hanno in P2.

La mia esigenza è quella di riportare nel foglio "indice" nelle cella da A2 a scendere i nomi dei fogli e da B2 a scendere il dato presente in ciascun foglio.

Ho "scritto" questa macro che soddisfa quasi la mia esigenza tranne per il fatto che non riesco a dirgli che riguardo il foglio 5 e il foglio 6 il dato da copiare si trova in P2 e non in O2.

Codice: Seleziona tutto

Sub elenca()
riga = 2
For Each SH In Sheets
If SH.Name <> "Indice" Then
Cells(riga, 1).Value = SH.Name
Cells(riga, 2).Formula = "=" & SH.Name & "!O2"
riga = riga + 1
End If
Next
End Sub

Mi potete aiutare?
Grazie Belvel

[Anthony47]

Ad esempio:

Codice: Seleziona tutto

Sub elenca()
Dim SH As Worksheet
'
riga = 2
For Each SH In Sheets
    If SH.Name <> "Indice" Then
        Cells(riga, 1).Value = SH.Name
        If SH.Name = "Foglio5" Or SH.Name = "Foglio6" Then
            Cells(riga, 2).Value = SH.Range("P2")
        Else
            Cells(riga, 2).Value = SH.Range("O2")
        End If
        Cells(riga, 2).Formula = "=" & SH.Name & "!O2"
        riga = riga + 1
    End If
Next
End Sub


Ciao

[Belvel]

Grazie Anthony

in verità non va, però grazie al tuo prezioso aiuto l'ho modificata come segue e va tutto alla grande.

Codice: Seleziona tutto

Sub elenca()
Dim SH As Worksheet
'
riga = 2
For Each SH In Sheets
    If SH.Name <> "Indice" Then
        Cells(riga, 1).Value = SH.Name
        If SH.Name = "Foglio5" Or SH.Name = "Foglio6" Then
            Cells(riga, 2).Formula = "=" & SH.Name & "!P2"
        Else
            Cells(riga, 2).Formula = "=" & SH.Name & "!O2"
        End If
        riga = riga + 1
    End If
Next
End Sub

Solo un ulteriore aiuto: se il riepilogo lo volessi su un file esterno come andrebbe modificata la macro?

Grazie ancora
Belvel

[Anthony47]

Se vuoi che la macro crei il riepilogo su un secondo file, allora apri anche il primo file; poi sul secondo file scrivi le formule che puntano al file giusto, al foglio giusto e alla cella giusta
Guarda con quale sintassi la formula "punta" al primo file e aggiungi la stringa che manca alle formule che crei.
Es, invece che Formula = "=" & SH.Name & "!O2"
.Formula = "='[NomeDelFile.xlsm]" & SH.Name & "!O2"

Se pensi di dover lavorare col primo file non aperto allora chiudilo e osserva come sono cambiate le formule, e adatta la prima stringa al percorso + nome file.

Ciao

[Belvel]

Ciao Anthony

credo di non aver capito quando dici "poi sul secondo file scrivi le formule che puntano al file giusto, al foglio giusto e alla cella giusta".

In pratica io ho scritto questa macro sia sul foglio d'origine (Prova_2) che su quello di destinazione (Indice). Ma da errore. Sicuramente ho sbagliato ma non capisco dove. Allego i 2 file:

http://www.filedropper.com/prova2
http://www.filedropper.com/indice_1

Codice: Seleziona tutto

Sub elenca()
Dim SH As Worksheet
'
riga = 2
For Each SH In Sheets
    If SH.Name <> "Indice" Then
        Cells(riga, 1).Value = SH.Name
        If SH.Name = "Foglio5" Or SH.Name = "Foglio6" Then
            Cells(riga, 2).Formula = "='[Indice.xlsm]" & SH.Name & "!O2"
        Else
            Cells(riga, 2).Formula = "='[Indice.xlsm]" & SH.Name & "!P2"
        End If
        riga = riga + 1
    End If
Next
End Sub

Grazie

[Anthony47]

Hai pubblicato due file vuoti, che devo farne?

[Belvel]

ciao Anthony

in entrambe i file io ho caricato la macro seguente, che però come ti dicevo no mi copia le celle O2 e P2 dal file di origine (Prova_2) a quello di destinazione (Indice_1).

Codice: Seleziona tutto

Sub elenca()
riga = 2
For Each SH In Sheets
If SH.Name <> "Indice" Then
Cells(riga, 1).Value = SH.Name
Cells(riga, 2).Formula = "=" & SH.Name & "!O2"
riga = riga + 1
End If
Next
End Sub

Grazie
Belvel

[Anthony47]

Non ti seguo...
Avevi gia' risolto il problema di creare in un foglio Indice il contenuto di alcune celle degli altri fogli

Adesso (per quello che avevo capito) si trattava di voler creare Indice su un file diverso da quello da cui si devono prendere i dati.
Per questo il mio suggerimeto era stato:

Se vuoi che la macro crei il riepilogo su un secondo file, allora apri anche il primo file; poi sul secondo file scrivi le formule che puntano al file giusto, al foglio giusto e alla cella giusta
Guarda con quale sintassi la formula "punta" al primo file e aggiungi la stringa che manca alle formule che crei.
Es, invece che Formula = "=" & SH.Name & "!O2"

Codice: Seleziona tutto

.Formula = "='[NomeDelFile.xlsm]" & SH.Name & "!O2"

Da quello che dici, il "secondo file" si chiama Indice.xlsm, e il "primo file" si chiama Prova_2.xlsm

Quindi, seguendo le vecchie istruzioni:
-metti su Indice.xlsm una formula che punta a Foglio1 di Prova_2; la formula sara' vista come

Codice: Seleziona tutto

=[Prova_2.xlsm]Foglio1!$O$2

-se pensi di dover lavorare anche con Prova_2 chiuso, chiudi prova_2; la formula ora e' vista come

Codice: Seleziona tutto

='C:\Percorso\[Prova_2.xlsm]Foglio1'!$O$2

Quindi nella tua macro, invece di .Formula = "=" & SH.Name & "!O2" dovrai inserire
.Formula = "='C:\Percorso\[Prova_2.xlsm]" & SH.Name & "'!O2"
(nota un '=apice prima del !)

C'E' TUTTAVIA da aggiungere che la macro che inserisce queste formule
1) va eseguita con Prova_2.xlsm aperto
2) deve essere modificata rispetto alla Sub elenca che avevi realizzato perche' il file su cui lavora (Indice.xlsm) e' diverso dal file a cui bisogna far riferimento (Prova_2.xlsm)

Insomma il codice da inserire sul file Indice.xlsm e' di questo tipo:

Codice: Seleziona tutto

Sub elencaExt()
Dim SH As Worksheet, WB2 As Workbook
'
Set WB2 = Workbooks("Prova_2.xlsm")     '<<< Il file di cui preparare il Riepilogo
riga = 2
For Each SH In WB2.Sheets
    If SH.Name <> "Indice" Then
        Cells(riga, 1).Value = SH.Name
        If SH.Name = "Foglio5" Or SH.Name = "Foglio6" Then
            Cells(riga, 2).Formula = "='C:\PercorsoReale\[Prova_2.xlsm]" & SH.Name & "'!P2"
        Else
            Cells(riga, 2).Formula = "='C:\PercorsoReale\[Prova_2.xlsm]" & SH.Name & "'!O2"
        End If
        riga = riga + 1
    End If
Next
End Sub

Modifica ='C:\PercorsoReale\[Prova_2.xlsm]" per adattare al tuo percorso e al tuo NomeFile

Ciao

[Belvel]

grazie Anthony, prezioso come sempre!!!!
Non ho parole per ringraziarti.
Va tutto alla perfezione

Grazie ancora
Belvel