Cartella lavoro: creare dei collegamenti dinamici tra i fogli.

Question

Ciao a tutti,

ho un problema col creare "finestre/fogli excel" all'interno di un foglio di lavoro della medesima cartella di lavoro.

Spiego il problema con un esempio banale. Ho una cartella di lavoro, con 3 fogli:

1. Il primo foglio raccoglie dei dati "globali". E' in realtà più una sorta di "cartiglio" per la stampa del documento finale; al suo interno ho il riferimento agli altri due fogli (numero 2 e 3) (=FoglioX!YN)
2. Sui fogli 2 e 3 raccolgo il brutto del lavoro... tante righe di dati in continuo aggiornamento.

Il problema è proprio questo aggiornamento.

Sul foglio 1 Globale ho due range di righe consecutive che racchiudono rispettivamente le informazione dei fogli 2 e 3. Purtroppo però non so come gestire dinamicamente il problema, cioè: supponiamo di avere alla data di oggi il seguente stato:

Foglio 2 -> 10 righe di dati

Foglio 3 -> 20 righe di dati

Il giorno dopo:

Foglio 2 -> 8 righe di dati (2 righe sono sparite perché sono informazioni di cui non ho più bisogno nello stampare il documento globale Foglio 1)

Foglio 3 -> 20 righe di dati

Mi ritrovo dunque 2 righe di interspazio tra le due finestre consecutive.

Sono così costretto ad eliminare le due righe.

Poi magari il giorno dopo ancora mi ritrovo con:

Foglio 2 -> 15 righe di dati

Foglio 3 -> 10 righe di dati

Allora devo inserire 7 nuove righe nel Foglio 1 per crearmi lo spazio, ricreare il riferimento per queste nuove 7 righe ai Fogli 2 e 3 e tutto torna... ma è un lavoro sinceramente evitabile? Ovvero, esiste un modo per gestire delle fantomatiche finestre DINAMICHE che si allungano dinamicamente in funzione del numero di righe che i 2 fogli hanno? Ovvero un sistema che riconosca DOVE i dati dei fogli 2 e 3 finiscono e quindi accorciare od allungare le finestre dinamicamente senza che sia costretto ogni volta ad aggiungere io manualmente nuove righe di riferimenti o eliminarle?

Grazie a tutti per l'aiuto e spero di essere stato esaustivo e chiaro.

Michele.

Answer 1

Non ho però capito se il Range destinato a Foglio2 e Foglio3 sul Foglio1 può cambiare, cioè se A4:H12 e A14:H30 sono fissi sul Foglio1 o se possono/devono espandersi/contrarsi.

     

Si, il Range destinato al Foglio 1 può cambiare cioè A4:H12 e A14:H30non sono fissi ma devono appunto espandersi/contrarsi (solo per il numero di righe però).

Ho provato tutte e due le macro: in quella di elianof, la riga 14 rimane fissa quando invece dovrebbe adattarsi alla fine del range che desume dal Foglio 2 (ma il resto è perfetto :-)), mentre nella macro di Gamberini la riga 14 viene "sovrascritta" qualora il range di dati nel foglio 2 sia superiore alle 9 righe (mi pare di aver capito che viene sovrascritto dal codice:

 sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A14")

In teoria bisognerebbe fare in modo che la CurrentRegion.Copy _ si riferisca ad una destinazione variabile e che non necessariamente deve essere copiata a partire da A14. Potrebbe essere A20, A200 etc etc.

Inoltre, se possibile, tra i vari Destination:=.Range("XYZ") dovrebbe rimanerci una riga bianca, o quantomeno qualcosa che mi faccia capire dove finisce una destination range e ne inizia un'alltra. Poi eventualmente per il cartiglio posso lavorare anche a mano.

 

Grazie ancora di tutto.

Public Sub m()

    'dichiaro le variabili

    Dim sh1 As Worksheet

    Dim sh2 As Worksheet

    Dim sh3 As Worksheet

    Dim rng As Range

    Dim lUltRiga As Long

    Dim s1 As String

    Dim s2 As String

    'nelle due stringhe metto cosa deve trovarsi

    'come intestazione dei dati dei fogli Foglio2

    'e Foglio3 in Foglio1(modifica a tuo piacimento)

    s1 = "Titolo dei dati derivanti dal Foglio2"

    s2 = "Titolo dei dati derivanti dal Foglio3"

    'metto un riferimento ai fogli

    With ThisWorkbook

        Set sh1 = .Worksheets("Foglio1")

        Set sh2 = .Worksheets("Foglio2")

        Set sh3 = .Worksheets("Foglio3")

    End With

    With sh1

        'trovo l'ultima riga utilizzata nel Foglio1

        lUltRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        'cancello il contenuto ed eventuali

        'formattazioni del Foglio1 da A3 a H(n)

        .Range("A3:H" & lUltRiga).Clear

        'inserisco in A3 l'intestazione per i dati

        'del Foglio2

        .Range("A3").Value = s1

        'copio/incollo i dati di Foglio2

        sh2.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A4")

        'trovo l'ultima riga utilizata adesso in Foglio1

        lUltRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        'inserisco in A(n) l'intestazione per i dati

        'del Foglio3 lasciando una riga bianca

        .Range("A" & lUltRiga + 2).Value = s2

        'copio/incollo i dati del Foglio3

        sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A" & lUltRiga + 3)

        'eventuali formattazioni alle intestazioni

        '(commenta o elimina il codice se non ti serve)

        '***INIZIO FORMATTAZIONE***

        Set rng = .Range("A3,A" & lUltRiga + 2)

        With rng.Font

            .Size = 14

            .ColorIndex = 3

            .Bold = True

            'ecc

        End With

        '***FINE FORMATTAZIONE***

    End With

    'Set a Nothing delle variabili oggetto

    Set rng = Nothing

    Set sh1 = Nothing

    Set sh2 = Nothing

    Set sh3 = Nothing

End Sub

Se(se) servono altre formattazioni in aree distinte del Foglio1, fai sapere quali e come vuoi formattarle. Grazie.

---

--

La soluzione, il codice ed i files sono forniti *così come sono* e l’autore declina ogni responsabilità per eventuali problemi causati dalla soluzione proposta se usata impropriamente. Create e utilizzate una copia del file per le vostre prove, *prima* di utilizzare la soluzione in files importanti.

--

Mauro Gamberini - Microsoft© MVP(Excel)

http://www.maurogsc.eu/

Answer 2

Allego il documento a questo percorso: www.wormsmania.net/doc_miky/esempio.pdf

In base a quello che vedo nel link, se ho capito bene e se i riferimenti sono corretti, prova:

Public Sub m()

    'dichiaro le variabili

    Dim sh1 As Worksheet

    Dim sh2 As Worksheet

    Dim sh3 As Worksheet

    'metto un riferimento ai fogli

    '(modifica i nomi con i tuoi")

    With ThisWorkbook

        Set sh1 = .Worksheets("Foglio1")

        Set sh2 = .Worksheets("Foglio2")

        Set sh3 = .Worksheets("Foglio3")

    End With

    With sh1

        'elimino i dati dalle celle del Foglio1 che

        'dovrebbero ricevere i dati dal

        'Foglio2(lascio eventuali formattazioni)

        .Range("A4:H12").Value = ""

        'elimino i dati dalle celle del Foglio1 che

        'dovrebbero ricevere i dati dal

        'Foglio3(lascio eventuali formattazioni)

        .Range("A14:H30").Value = ""

        'copio i dati dal Foglio2 e li incollo

        'nel Foglio1 a partire da A4

        sh2.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A4")

        'copio i dati dal Foglio3 e li incollo

        'nel Foglio1 a partire da A14

        sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A14")

    End With

    'Set a Nothing delle variabili oggetto

    Set sh1 = Nothing

    Set sh2 = Nothing

    Set sh3 = Nothing

End Sub

Non ho però capito se il Range destinato a Foglio2 e Foglio3 sul Foglio1 può cambiare, cioè se A4:H12 e A14:H30 sono fissi sul Foglio1 o se possono/devono espandersi/contrarsi.

Il codice è commentato in modo che tu possa capire cosa fanno le singole istruzioni.

---

--

La soluzione, il codice ed i files sono forniti *così come sono* e l’autore declina ogni responsabilità per eventuali problemi causati dalla soluzione proposta se usata impropriamente. Create e utilizzate una copia del file per le vostre prove, *prima* di utilizzare la soluzione in files importanti.

--

Mauro Gamberini - Microsoft© MVP(Excel)

http://www.maurogsc.eu/

Answer 3

Ciao Fratello.

Probabilmente "cartiglio" è una zona del foglio1 contenente fincature ed intestazioni adatte per stampare un report, al quale vanno aggiunti i dati rilevati dagli altri fogli.

Confermo che non fa parte degli oggetti di XL, ma quel termine "tecnico" veniva usato anche dagli antichi Egizi, miei contemporanei.:-))

Saluti

Eliano

Answer 4

Public Sub m()   

 Dim sh1 As Worksheet

    Dim sh2 As Worksheet

    Dim sh3 As Worksheet

    Dim lUltRiga As Long

   

    With ThisWorkbook

        Set sh1 = .Worksheets("Foglio1")

        Set sh2 = .Worksheets("Foglio2")

        Set sh3 = .Worksheets("Foglio3")

    End With

   

    With sh1

        .Cells.Clear

        sh2.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A1")

        lUltRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A" & lUltRiga + 2)

    End With

   

    Set sh1 = Nothing

    Set sh2 = Nothing

    Set sh3 = Nothing

   

End Sub

Rieccomi. Ci sono due cose che non capisco e vorrei provarci anche io a modificare il codice, se solo capissi un qualcosina di VB.

Con questa macro mi scompare il cartiglio che ho sul Foglio 1... quindi quel .Cell.Clear come dovrei correggerlo per far sì che non cancelli le celle che non vengono sovrapposte? Teoricamente i worksheet2 e 3 dovrebbero lavorare come una sorta di "finestra" sul worksheet1.

Infatti chiedo inoltre se sia possibile anche definire un intervallo di colonne: cioè ora il riferimento dinamico ai dati dei vari fogli è giusto, ma se volessi delimitare queste righe alla colonna X come e/o cosa dovrei fare?

Grazie ancora.

Forse è meglio capire bene. Cartiglio non fa parte delle librerie di oggetti di Excel, per favore spiega(bene) cosa intendi, grazie. Tu hai davanti il tuo file, noi no. Ora Foglio2 e Foglio3 possono avere n righe, ma su quali colonne? Vuoi riportare queste righe sul Foglio1, ok? Se è così, dove riportiamo quei dati, cioè da dove partiamo(riferimento righe e colonne)? Inoltre, se abbiamo dei dati precedenti sul Foglio1, che ne facciamo? Li teniamo come se fossero una specie di storico? Li cancelliamo e li sostituiamo? Grazie per l'attenzione.

---

--

La soluzione, il codice ed i files sono forniti *così come sono* e l’autore declina ogni responsabilità per eventuali problemi causati dalla soluzione proposta se usata impropriamente. Create e utilizzate una copia del file per le vostre prove, *prima* di utilizzare la soluzione in files importanti.

--

Mauro Gamberini - Microsoft© MVP(Excel)

http://www.maurogsc.eu/

Answer 5

Public Sub m()   

 Dim sh1 As Worksheet

    Dim sh2 As Worksheet

    Dim sh3 As Worksheet

    Dim lUltRiga As Long

   

    With ThisWorkbook

        Set sh1 = .Worksheets("Foglio1")

        Set sh2 = .Worksheets("Foglio2")

        Set sh3 = .Worksheets("Foglio3")

    End With

   

    With sh1

        .Cells.Clear

        sh2.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A1")

        lUltRiga = .Range("A" & .Rows.Count).End(xlUp).Row

        sh3.Range("A1").CurrentRegion.Copy _

            Destination:=.Range("A" & lUltRiga + 2)

    End With

   

    Set sh1 = Nothing

    Set sh2 = Nothing

    Set sh3 = Nothing

   

End Sub

Rieccomi. Ci sono due cose che non capisco e vorrei provarci anche io a modificare il codice, se solo capissi un qualcosina di VB.

Con questa macro mi scompare il cartiglio che ho sul Foglio 1... quindi quel .Cell.Clear come dovrei correggerlo per far sì che non cancelli le celle che non vengono sovrapposte? Teoricamente i worksheet2 e 3 dovrebbero lavorare come una sorta di "finestra" sul worksheet1.

Infatti chiedo inoltre se sia possibile anche definire un intervallo di colonne: cioè ora il riferimento dinamico ai dati dei vari fogli è giusto, ma se volessi delimitare queste righe alla colonna X come e/o cosa dovrei fare?

Grazie ancora.