[Excel] Macro copia-incolla righe

[ilfuocoblu]

salve, è il mio primo intervento ma vi leggo da un bel pò.
premetto che ho cercato a lungo senza trovare la soluzione.

dunque, ho foglio1 in cui importo dei dati da una query web. nello stesso vengono fatti dei calcoli tramite formule.
ora, avrei bisogno di una macro che ad ogni aggiornamento (temporizzato) della query mi copi determinate righe intere nel foglio2 accodandoli di volta in volta. in pratica mi interessa conservare uno storico delle righe in cui in colonna G sia presente il valore zero.

se mi dovessi esprimere con una formula sarebbe: cerca in foglio1 colonna G il valore zero, copia la riga relativa (solo valori) e incollala nel foglio2 dopo l'ultima riga con valori.

chi mi può aiutare?

[ricky53]

Ciao,
prima di tutto benvenuto nel nostro forum.


Dovresti allegare altre informazioni sulla query ed allegare le formule principali che utilizzi
Che conoscenze hai del VBA? Sai programmare?
Quali/Quante sono le righe, colonne, celle da copiare?

[Flash30005]

Ciao IlFuocoBlu e benvenuto nel Forum

Ho realizzato questa macro-tipo che adatterai alle tue esigenze

Codice: Seleziona tutto

Sub Archivia()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Set Ws1 = Sheets("Foglio1")
Set Ws2 = Sheets("Foglio2")
UR1 = Ws1.Range("A" & Rows.Count).End(xlUp).Row
For RR1 = 2 To UR1
    If Ws1.Range("G" & RR1).Value = 0 Then
        UR2 = Ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
        Ws1.Rows(RR1).Copy
        Ws2.Range("A" & UR2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        End If
Next RR1
Application.CutCopyMode = False
End Sub

Sia Ur1 sia UR2 leggono il numero delle righe nella colonna A quindi assicurati innanzitutto quale sia la colonna che ha più righe e sostituisci la lettera della colonna al codice (esempio se fosse colonna "B")
UR1 = Ws1.Range("B" & Rows.Count).End(xlUp).Row (anche per UR2)

La macro va richiamata dopo la query web (con una call) altrimenti accoderai di nuovo le righe già registrate
per evitare questo è possibile mettere un flag nel foglio1 (esempio in Z1) ma la query web dovrà eliminare tutti i dati del foglio1 con Cells.clear
prima dell'avvio macro di importazione dati dal web.

Ciao

EDIT: Corretta macro ore 1:00 del 05/02/2015

[Anthony47]

In alternativa alla macro suggerita da Flash (Vedi sopra) suggerisco di usare l' evento AfterRefresh della web query, come avevo gia' suggerito qui: http://www.pc-facile.com/forum/viewtopic.php?t=98829

Il codice suggerito va inserito nel modulo di codice di Foglio1 (click destro sul tab col nome Foglio1; scegli Visualizza codice; incollare il codice nel frame di dx).
Ho immaginato, in assenza di tue indicazioni, che la cella B2 di Foglio1 sia all' interno dei dati della query.
Poiche' quello che tu vorresti fare dopo il refresh e' diverso da quanto voleva fare AntonioPatella nella discussione che ti ho linkato, la tua Private Sub qt_AfterRefresh(ByVal Success As Boolean) avra' questo codice:

Codice: Seleziona tutto

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
If Not Success Then Exit Sub

Dim LastSh2 As Long, Cell As Range, Sh1
'
With Sheets("Foglio1")
    If Application.Intersect(.Range("B2").CurrentRegion, .Range("G:G")) Is Nothing Then Exit Sub
'
    For Each Cell In Application.Intersect(.Range("B2").CurrentRegion, .Range("G:G"))
        If Cell.Value = 0 Then
            Cell.EntireRow.Copy
            Sheets("Foglio3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End If
    Next Cell
End With
End Sub


In questo modo la teoria vuole che dopo ogni refresh si avvii la Sub qt_AfterRefresh, che dovrebbe fare le copie richieste.

Prova (anche questa) e fai sapere.

Ciao

[ilfuocoblu]

innanzi tutto un grazie per la disponibilità
ho provato i codici sia di flash che di anthony ma purtroppo senza esito.
forse ho dimenticato di aggiungere che non sono certo un guru di excel
dunque, importo in foglio1 una query che preleva una tabella di quote sportive, questa viene aggiornata ogni 15 min dalle proprietà stesse della query. ho dovuto inserire solo 2 codici: functio colorecella per il riconoscimento del colore della cella, appunto. e poi un codice per il ricalcolo, dato che dopo l'aggiornamento, le celle di riconoscimento colore visualizzavano asterischi e dovevo manualmente cliccare qualcosa per visualizzare i dati.
la tabella popola un centinaio di righe (più o meno, dipende dal palinsesto) e fino alla colonna E.
dalla colonna J alla M ho le formule che stabiliscono determinate condizioni (semplici formule SE).
nella colonna G ho il "riassunto" dei calcoli delle formule relative alla sua riga che mi segnala con lo ZERO se ci sono le condizioni che voglio, altrimenti la cella resta vuota.
ad ogni refresh temporizzato della query vorrei che la macro mi riportasse i valori delle righe aventi in G lo zero nel foglio2 accodandole, dato che al via dell'evento sportivo le quote vengono azzerate e non mi resta uno storico da poter valutare.

spero di essere stato abbastanza chiaro. portate pazienza

[Flash30005]

Non so cosa tu abbia fatto ma la macro che ti ho inviato copia tutte le righe che nel foglio della query (Foglio1) trova lo 0 (zero) nella colonna G
Ora scarica il file che allego
apri e ti troverai nel foglio2 (completamente vuoto)
ti posizioni sul Foglio1 (dati casuali ma che servono come test)
e avvii la macro tramite comnando "Avvia Macro"
torni nel foglio2 e lo troverai popolato delle righe che nel foglio1 avevano lo zero nella colonna G
Download file

ciao

[Anthony47]

ho provato i codici sia di flash che di anthony ma purtroppo senza esito.
forse ho dimenticato di aggiungere che non sono certo un guru di excel

Tu non sarai un guro ma noi (mi sento di parlare anche per Flash) non siamo stregoni che capiamo anche le cose non dette...
E non avendo descritto niente del tuo foglio ad esempio io ho fatto delle congetture che non hai ne' confermato ne' smentito.

Nella macro di Flash (vedi http://www.filedropper.com/generastorico_1) credo che la riga Ws1.Rows("4:4").Copy debba essere sostituito con

Codice: Seleziona tutto

Ws1.Rows(RR1).Copy

Il problema che vedo e' che qualcuno deve avviare la macro dopo il refresh della web query

Comunque a questo punto ti suggerisco di cambiare approccio: una web query non temporizzata (rinunciando quindi all' uso dell' evento AfterRefresh) ma una macro che si attiva ogni tot minuti usando il "metodo On Time" e che al suo interno fa un refresh della query e poi lancia o il codice contenuto nella mia Sub qt_AfterRefresh o la Sub Archivia contenuta nel file di Flash (devi solo assicurarti che i dati della web query cominciano da riga 2).
Questo sistema era stato descritto in questa discussione:
http://www.pc-facile.com/forum/viewtopi ... 25#p540318

L' alternativa e' che pubblichi il tuo file attuale, contenente la vera web query, e lavoriamo su quello.

Ciao a tutti.

[ilfuocoblu]

grazie per la pazienza, in effetti avevo dimenticato di aggiungere che la query è posizionata in a4, popola fino alla colonna E e la lunghezza delle righe è variabile (solitamente un centinaio o poco più).
in effetti il file gentilmente linkato da Flash fa esattamente quello che mi serve, solo che mi riporta anche le righe con celle in colonna G vuote.
ad esempio:

col.A...col.G
aaa......0
bbb.....vuota
ccc.......1

mi riporta in foglio2: aaa(corretto)...bbb(errato)

quindi, riepilogando:
la query è fissata in a4 (le prime 3 righe sono vuote)
righe con celle G vuote da non cosiderare
cosa devo correggere nel codice?

e un'ultima cosa, poi penso che ce la posso fare:
a me non importa che questo codice sia strettamente legato ad altri o alla query, mi basterebbe che facendolo partire, poi si avviasse periodicamente, tipo ogni 15 min. (OnTime Now + TimeValue ??). se poi mi ritrovo doppioni da scremare in foglio2 non è un problema, poi li posso filtrare a mano.

[Flash30005]

Sostituisci la riga

Codice: Seleziona tutto

    If Ws1.Range("G" & RR1).Value = 0 Then

con

Codice: Seleziona tutto

    If Ws1.Range("G" & RR1).Value = 0 And Ws1.Range("G" & RR1).Value <> "" Then

Inoltre ti consiglio di attivare la macro tramite una call dalla macro che effettua importazione dati in quanto già temporizzata ed è inutile inserire un'altra temporizzazione quindi avrai

Codice: Seleziona tutto

sub TuaMacroQueryWev()
'...codice macro queryweb
'....

Call Archivia '<<<< inserire qui la chiamata alla macro Archivia
End Sub


ciao

[ilfuocoblu]

bene, adesso tutto funziona perfettamente!
per non pasticciare ulteriormente ho solo aggiunto al codice un ontime, e fa esattamente ciò che mi serve!

non ho parole per la pazienza e la disponibilità di questo forum!
grazie ad Anthony e a Flash!

[Anthony47]

Se ora hai una macro OnTime (che immagino usi per eseguire ogni N minuti la macro di Flash) allora ti suggerisco:
1) modifica la query per eliminare il refresh ogni N minuti
2) in testa alla macro che si avvia "on time" metti l' istruzione

Codice: Seleziona tutto

ThisWorkbook.Sheets("Foglio1").Range("B5").QueryTable.Refresh BackgroundQuery:=False

In questo modo la stessa macro aggiorna la web query e subito dopo salva i dati che ti interessano.

Attenzione: la macro OnTime si eseguira' indipendentemente da quale file e' in quel momento attivo, quindi tutte le azioni devono essere esplicitamente indirizzate al giusto file se non vuoi ottenere risultati potenzialmente distruttivi. Se la macro e' contenuta nello stesso file della web query dovrebbe bastare inserire "ThisWorkbook" per qualificare correttamente fogli e intervalli da indirizzare.
Se non riesci allora pubblica il codice complessivo della macro che si esegue e lo faremo insieme.

Ciao

[ilfuocoblu]

salve amici, ho bisogno nuovamente del vostro aiuto riguardo a questo 3D.
la macro di Flash funziona perfettamente. dovrei fare una modifica, cosa devo modificare per far si che a condizioni rispettate questa riga di codice "UR2 = Ws2.Range("A" & Rows.Count).End(xlUp).Row + 1" invece di copiare tutta la riga mi copi soltanto le celle a-d-m-p ?
grazie.

[Anthony47]

Quella riga non centra con l'area copiata; la riga da modificare e' Ws1.Rows(RR1).Copy
Che diventa

Codice: Seleziona tutto

Ws1.Range("A" & RR1 & ", D" & RR1 & ", M" & RR1 & ", P" & RR1).Copy

Ciao

[ilfuocoblu]

ecco, erano gli apici alla fine di ogni cella che omettevo.
funziona ovviamente, sempre gentili e disponibili.
grazie Anthony!