Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 10:16

Ciao a tutti,
sto cercando di creare una macro che per me è abbastanza difficile. Ho frugato tra le discussioni del forum ma non ho trovato nulla che facesse al caso mio. Ho trovato molti spunti che non sono riuscito ad assemblare correttamente per risolvere il mio problema.
Spero di riuscire a spiegarmi nel migliore dei modi.

Ho un file di excel che si compone di vari fogli. Il nome di ogni foglio è un numero progressivo di tre cifre (da 001 a 999).

Con una macro (trovata nel forum che ho già adattato al mio caso) creo un foglio nuovo copiando il precedente. Il nome del nuovo foglio è (come detto) il numero progressivo successivo al foglio che ho copiato.

=== FIN QUI HO TUTTO E FUNZIONA TUTTO ===

Oltre ai fogli nominati col numero progressivo da 001 a 999 esiste un foglio chiamato "dettaglio" nel quale sono contenuti dei dati estrapolati da tutti i fogli con una formuila semplice di richiamo cella (tipo questa: ='001'!$AA$53).

nel foglio "dettaglio" ho creato una tabella che raccoglie tutti questi dati; ora vorrei che questi dati fossero inseriti appena creo un foglio nuovo.

la sequenza della macro che vorrei è questa:

- appena creato il nuovo foglio (con la macro che ho gà e ce funziona bene)

- vai nel foglio "dettaglio"
- seleziona il range C5:R5 e copia le celle
- incollale nella prima riga disponibile a partire dall'alto sempre dalla colonna C (un tot di righe più in basso per capirci)
- nelle celle che hai appena incollato sostituisci "001" con il "nome del foglio appena creato" (che sarà un numero a tre cifre compreso tra 001 e 999).

Grazie mille a tutti coloro che risponderanno.

brikki
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Sponsor
 

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 04/04/16 11:32

Ciao,
puoi riportare la macro che utilizzi?

Inoltre, un file di esempio con la situazione reale ma SENZA dati riservati sarebbe utilissimo.
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 11:55

ricky53 ha scritto:Ciao,
puoi riportare la macro che utilizzi?

Inoltre, un file di esempio con la situazione reale ma SENZA dati riservati sarebbe utilissimo.


Certamente!
la macro è questa (è un misto di alcune cose che ho trovato sul forum, non è roba mia, quindi non so spiegartela nel dettaglio):

Codice: Seleziona tutto
Sub Nuova()
ActiveSheet.Unprotect
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = [BC8]

'copia numero distinta
    ActiveSheet.Previous.Select
    Range("BC9").Select
    Selection.Copy
    ActiveSheet.Next.Select
    Range("e11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'fine copia numero distinta
   
    ActiveSheet.Previous.Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("a1").Select
    ActiveSheet.Next.Select
   
'inserisci una data
    Range("BC10").Select
    Selection.Copy
    Range("d10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'fine inserisci una data

'pulisci nuova distinta
    Range("B18:AO51").Select
    Selection.ClearContents
    Range("AQ18:AO51").Select
    Selection.ClearContents
    Range("AU18:AU51").Select
    Selection.ClearContents
'fine pulisci nuova distinta



Range("a1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


qui invece trovi il file:
http://www.bassottofilippo.it/richiFiles/esempioEXCEL.zip

GRAZIE! :)
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 04/04/16 14:08

Ciao,
vediamo se è così:
tu vuoi copiare nel foglio "Dettaglio" le formule in Cnn:Rnn (con "nn" intendo identificare l'ultima riga con formule) nella riga successiva e sostituire il nome del foglio contenuto in queste formule con il nome del foglio "APPENA" inserito?
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 14:29

è esattamanete così.

bravissimo!
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 04/04/16 14:32

Ciao,
allora prova questo codice che dovrai eseguire dopo aver utilizzato il codice che utilizzi tu.

Codice: Seleziona tutto
Option Explicit

Sub Copia_Formule()
    Dim UR As Integer, I As Integer, Nome_Pre As String, Nome_Att As String
   
    Sheets("dETTAGLi0").Select
    ActiveSheet.Unprotect

    UR = Range("C" & Rows.Count).End(xlUp).Row
    Range("C" & UR & ":R" & UR).Copy Destination:=Range("C" & UR + 1)
    Nome_Pre = Format(Left(Range("C" & UR), 3), "000")
    Nome_Att = Format(Val(Nome_Pre) + 1, "000")
   

    For I = 3 To 18
        Cells(UR + 1, I).Replace What:=Nome_Pre, Replacement:=Nome_Att
    Next I
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   
    MsgBox "E' stata effettuata la copia delle formule e la sostituzione del nome del foglio", vbInformation
End Sub
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 15:05

Grazie! :)
Sto provando ad utilizzare il codice ma non riesco a farlo funzionare e non capisco dove sbaglio.
Ho incollato il tuo codice dopo la macro che ho usato per duplicare il foglio.
mi appare il messaggio ma non vedo la nuova riga di celle copiate e incollate nel foglio dettaglio...
:\
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 04/04/16 15:37

Ciao,
esegui il codice con "F8" (passo passo) e vedrai quali istruzioni esegue.
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 15:56

mi evidenzia "Option Explicit" e non mi fa eseguire la macro:
Appare un popup con la scritta
Errore di compilazione:
Non valido all'interno di una routine
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 15:56

ecco come l'ho inserita:

Codice: Seleziona tutto
Sub Nuova()
ActiveSheet.Unprotect
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = [BC8]

'copia numero distinta
    ActiveSheet.Previous.Select
    Range("BC9").Select
    Selection.Copy
    ActiveSheet.Next.Select
    Range("e11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'fine copia numero distinta
   
    ActiveSheet.Previous.Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("a1").Select
    ActiveSheet.Next.Select
   
'inserisci una data
    Range("BC10").Select
    Selection.Copy
    Range("d10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'fine inserisci una data

'pulisci nuova distinta
    Range("B18:W51").Select
    Selection.ClearContents
    Range("AA18:AO51").Select
    Selection.ClearContents
    Range("AQ18:AQ51").Select
    Selection.ClearContents
    Range("AU18:AU51").Select
    Selection.ClearContents
'fine pulisci nuova distinta

    Option Explicit

    Sub Copia_Formule()
        Dim UR As Integer, I As Integer, Nome_Pre As String, Nome_Att As String
       
        Sheets("dETTAGLi0").Select
        ActiveSheet.Unprotect

        UR = Range("C" & Rows.Count).End(xlUp).Row
        Range("C" & UR & ":R" & UR).Copy Destination:=Range("C" & UR + 1)
        Nome_Pre = Format(Left(Range("C" & UR), 3), "000")
        Nome_Att = Format(Val(Nome_Pre) + 1, "000")
       

        For I = 3 To 18
            Cells(UR + 1, I).Replace What:=Nome_Pre, Replacement:=Nome_Att
        Next I
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
       
        MsgBox "E' stata effettuata la copia delle formule e la sostituzione del nome del foglio", vbInformation
    End Sub

Range("a1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 04/04/16 16:04

Ciao,
è preferibile NON inserirla nel tuo codice ma fuori.
A) Copia il codice che ti ho inviato in un nuovo Modulo.
B) Scrivi
Codice: Seleziona tutto
Call Copia_Formule

come ultima istruzione della tua SUB di nome "Nuova"
C) Esegui la tua sub "Nuova"

Se proprio vuoi averla nulla tua sub devi cancellare
Codice: Seleziona tutto
    Option Explicit

    Sub Copia_Formule()

ed alla fine devi cancellare
Codice: Seleziona tutto
    End Sub

che è subito dopo
Codice: Seleziona tutto
MsgBox "E' stata effettuata la copia delle formule e la sostituzione del nome del foglio", vbInformation



Io ti consiglio la mia soluzione !!!
Poi vedi tu.
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 16:35

Ho fatto esattamente come dici tu.
Sto eseguendo la macro con F8 però, da qui

Codice: Seleziona tutto
        UR = Range("C" & Rows.Count).End(xlUp).Row


in poi, sembra che non esegua nulla. infatti nel foglio "Dettaglio", nel range C5:R5, non ci sono altre righe se non la prima che è quella che deve essere copiata e incollata.
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 04/04/16 16:45

Ciao,
Sei andato avanti con altri "F8" ?
Ad ogni "F8" si sposta la riga evidenziata in giallo?
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 04/04/16 18:13

si, si sposta. va in basso alla successiva riga
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 04/04/16 20:13

Ciao,
BENE.
Devi andare avanti "passo passo", ossia con TANTI "F8", fine alla fine della SUB
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 05/04/16 07:08

è esattamente quello che faccio.
ma quando arriva a "Next I" ricomincia da "Cells(UR + 1, I).Replace What:=Nome_Pre, Replacement:=Nome_Att" per 16 volte e non succede nulla. cioè la macro non copia e incolla le celle nella prima riga utile e non sostituisce i valori nelle celle copiate.
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 05/04/16 07:49

sCUSAMI,
ho trovato l'errore che facevo: c'erano delle celle non vuote e la macro incollava (giustamente) alla fine. Mi trovavo per cui le celle incollate alla riga 2000 e passa e non le vedevo.
ora funziona l'istruzione che incolla le celle ma resta sempre il problema della "sostituzione". non viene effettuata la sostituzuione col nome del foglio "appena" creato.
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 05/04/16 08:00

ok, RISOLTO.
ti ringrazio per la pazienza e la disponibilità; non sai che favore mi hai fatto!!! :).
adesso posto tutto quanto.
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi brikki » 05/04/16 08:14

Allora: ho unito le due macro come mi hai detto tu: richiamando la seconda all'interno della prima.

Sul forum ho trovato questa:
Codice: Seleziona tutto
Sub ultimoFolio()
For I = 2 To Sheets.Count
Sheets(I).Select
Next I
End Sub


che ho inserito perché mi serviva che alla fine dell'esecuzione della macro apparisse l'ultimo foglio.

in sostanza con le macro che scriverò di seguito ottengo questo:
- duplico un foglio e cancello i dati che non mi servono nel nuovo foglio
- evidenzio il foglio archivio nel quale copio una riga di dati e la incollo nella riga libera successiva
- evidenzio questa riga appena copiata e sostituisco i dati con Cerca/Sostituisci.

Macro COMPLETA:

Codice: Seleziona tutto
Call ultimoFolio
ActiveSheet.Unprotect
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = [BC8]

'copia numero distinta
    ActiveSheet.Previous.Select
    Range("BC9").Select
    Selection.Copy
    ActiveSheet.Next.Select
    Range("e11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'fine copia numero distinta
   
    ActiveSheet.Previous.Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("a1").Select
    ActiveSheet.Next.Select
   
'inserisci una data
    Range("BC10").Select
    Selection.Copy
    Range("d10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("a1").Select
'fine inserisci una data

'pulisci nuova distinta
    Range("B18:W51").Select
    Selection.ClearContents
    Range("AA18:AO51").Select
    Selection.ClearContents
    Range("AQ18:AQ51").Select
    Selection.ClearContents
    Range("AU18:AU51").Select
    Selection.ClearContents
'fine pulisci nuova distinta

Call Copia_Formule
Call ultimoFoglio

Range("a1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub


per copiare le formule:

Codice: Seleziona tutto
Option Explicit

    Sub Copia_Formule()
        Dim UR As Integer, I As Integer, Nome_Pre As String, Nome_Att As String
       
        Sheets("dETTAGLi0").Select
        ActiveSheet.Unprotect

        UR = Range("D" & Rows.Count).End(xlUp).Row
        Range("C" & UR & ":T" & UR).Copy Destination:=Range("C" & UR + 1)
        Nome_Pre = Format(Left(Range("C" & UR), 3), "000")
        Nome_Att = Format(Val(Nome_Pre) + 1, "000")
       

        For I = 3 To 18
            Cells(UR + 1, I).Replace What:=Nome_Pre, Replacement:=Nome_Att
        Next I
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
       
    End Sub


e per evidenziale l'ultimo foglio:

Codice: Seleziona tutto
Sub ultimoFoglio()
For I = 2 To Sheets.Count
Sheets(I).Select
Next I
End Sub


GRAZIE 1000
:D :D :D :D :D :D


(come faccio a mettere "[RISOLTO]" nel titolo?)
brikki
Utente Junior
 
Post: 23
Iscritto il: 04/01/12 11:34

Re: Macro EXCEL : Copia-Incolla e Trova-Sostituisci

Postdi ricky53 » 05/04/16 11:59

Ciao,
per selezionare l'ultimo foglio non occorre il ciclo, basta la seguente istruzione
Codice: Seleziona tutto
Sheets(Worksheets.Count).Select


CONSIGLIO: quando le istruzione di una SUB sono poche NON vale la pena di utilizzare la "Call ....." ma è preferibile (per migliorare la leggibilità) inserire le istruzioni dove occorrono.
Per esempio nel caso di selezione ultimo foglio elimina l'istruzione
"Call ultimoFolio"
e scrivi, al suo posto, l'istruzione che ti ho fornito io.



In questo forum possono modificare il titolo di una discussione solo i Moderatori.
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W7; Office 2003-10-13-16
Avatar utente
ricky53
Utente Senior
 
Post: 4223
Iscritto il: 11/04/09 19:29
Località: Italia

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "Macro EXCEL : Copia-Incolla e Trova-Sostituisci":


Chi c’è in linea

Visitano il forum: Nessuno e 10 ospiti