› Sviluppare funzionalita su Microsoft Office con VBA › Errore: Metodo 'Range' dell'oggetto '_Worksheet' non riuscito
-
AutoreArticoli
-
Buongiorno a tutti,
Inanzi tutto sto cercando di creare un codice che faccia comunicare 2 fogli di 2 file diversi di Excel. In poche parole sto scrivendo un codice che riesca a copiare i valori della tabella presente nel foglio 1 del file Prova.xlsm e copiare SOLO I VALORI nella prima riga disponibile nel foglio "Raccolta Dati" di Prova_Rete.xlsx.
inoltre vorrei aggiungere al codice, ma metto già le mani avanti perchè non sono capace, la cancellazione delle prime 2 colonne della tabella nel foglio 1.
io ho scritto questo codice:
Sub CopiaRete() Dim WK1 As Workbook, WK2 As Workbook Dim sh1 As Worksheet, sh2 As Worksheet Dim uCol As Integer, Ur As Integer Application.ScreenUpdating = False Set WK1 = ThisWorkbook Set WK2 = Workbooks.Open("C:\Users\d.castagna\Desktop\Assistenze\Prove\Prova_Rete.xlsx") Set sh1 = WK1.Worksheets("Foglio1") Set sh2 = WK2.Worksheets("Raccolta Dati") Ur = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1 ' ricerca prima riga libera in IR2 e memoeizzo in ur uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column sh1.Range(sh1.Cells(1, 1), sh1.Cells(1, uCol)).Copy sh2.Range(sh2.Cells(Ur, 1)).PasteSpecial xlPasteValues Application.CutCopyMode = False WK2.Save WK2.Close Application.ScreenUpdating = True Set sh2 = Nothing Set sh1 = Nothing Set WK1 = Nothing Set WK2 = Nothing End Sub
mi da questo errore: Metodo 'Range' dell'oggetto '_Worksheet' non riuscito
credo che il problema sia l'uso del comando PasteSpecial, ho provato a fare una decina di codici diversi ma non riesco a farne andare nemmeno uno.
il debug mi da erore in questa riga di codice:
sh2.Range(sh2.Cells(Ur, 1)).PasteSpecial xlPasteValues
Allego i file come esempio (sono degli esempi causa privacy).
Grazie in anticipo
Allegati:
You must be logged in to view attached files.Quel sh2.Range costruito così non mi piace molto, normalmente ti funziona?
normalmente funziona, per esempio nella riga superiore non mi da nessun errore..
sh1.Range(sh1.Cells(1, 1), sh1.Cells(1, uCol)).Copy
Il codice che proponi è diverso:
sh1.range(cella, cella).copy
vs
sh2.range(cella).pastespecial
un errore era quello, adesso però mi copia solo l'intestazione della tabella e non me la copia alla prima riga libera ma mi sostituisce la tabella già esistente in "Raccolta Dati" e in ogni riga mi copia l'intestazione della tabella..
questo è il codice:
Sub CopiaRete() Dim WK1 As Workbook, WK2 As Workbook Dim sh1 As Worksheet, sh2 As Worksheet Dim uCol As Integer, Ur As Integer Application.ScreenUpdating = False Set WK1 = ThisWorkbook Set WK2 = Workbooks.Open("C:\Users\d.castagna\Desktop\Assistenze\Assistenze_Rete.xlsm") Set sh1 = WK1.Worksheets("Foglio1") Set sh2 = WK2.Worksheets("Raccolta Dati") Ur = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1 uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column sh1.Range(sh1.Cells(1, 1), sh1.Cells(1, uCol)).Copy sh2.Range(sh2.Cells(1, 1), sh2.Cells(Ur, 1)).PasteSpecial xlPasteValues Application.CutCopyMode = False Application.ScreenUpdating = True Set sh2 = Nothing Set sh1 = Nothing Set WK1 = Nothing Set WK2 = Nothing End Sub
i nomi dei file sono diversi perchè sono i file originali.
sh1.Range(sh1.Cells(1, 1), sh1.Cells(1, uCol)).Copy
sh2.Range(sh2.Cells(1, 1), sh2.Cells(Ur, 1)).PasteSpecial xlPasteValuesAllora, da qui capisco questo:
"prendi dal foglio1 il range (solo valori) in A1:(ultimacolonna)1 e copialo nel range A1:A(ultimariga)"
Perciò il codice non sbaglia: preleva l'intestazione (riga 1, colonna A:ultimacolonna) e la ricopia paro paro in tutto il range del foglio 2 che va da A all'ultima riga compilata.
Quindi: tu devi "copyare" il range di celle che va da A1 a Aultimariga del foglio1, nella destinazione del foglio2 che inizia alla cella A(ultimariga+1).
A quest'ultimo proposito, la riga che ti dava errore prima: "sh2.Range(sh2.Cells(Ur, 1)).PasteSpecial xlPasteValues" è errata solo perchè premetti a Cells un range. Toglilo e vedrai che funziona 🙂
caspita, ho scritto il contrario di quello che volevo, perchè ho visto che se metto range(Tabella2) (che sarebbe la tabella che mi dovrebbe copiare) mi da errore, quindi a me interesserebbe copiare tutta la tabella (che con il tempo andrà ad espandersi, ma senza intestazione.
scusa se insisto ma sono un principiante, ho cercato ovunque ma non trovo un codice che faccia questo.
ho risolto il problema della prima riga disponibile così
sh2.Range("A65536").End(xlUp).Offset(1, 0).Select
mancherebbe solo il "copiami la tabella 2 ma sensa intestazioni"
se riesci a darmi uno spunto te ne sarei molto grato
togliendolo mi copia comunque l'intestazione, credo ci sia qualcosa di sbagliato nella variabile uCol..
uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
se riesci a darmi uno spunto
Ho visto che hai scoperto il trucco con Offset, molto bene, ti servirà perchè lo spunto che ti do adesso è: "seleziona l'intera regione corrente della tabella a partire da A1, spostati una riga in basso per evitare l'intestazione, ridimensiona la selezione per evitare l'ultima riga vuota, quindi copia tutto nel foglio2".
Concetti da approfondire: CurrentRegion, Offset, Resize. La guida in linea è esaustiva per tutti questi metodi comunque il primo seleziona tutto un range di celle contigue, il secondo opera uno scarto di tot righe e colonne, il terzo ridimensiona una selezione.
sh1.Range("A2").CurrentRegion.Select
così mi seleziona senza intestazione? perchè mi da errore
scusa se insisto, ma c'è qualche errore nella variabile uCol?
uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
perchè in teoria è qui il problema..
così mi seleziona senza intestazione? perchè mi da errore
Non è esatto, l'istruzione seleziona tutta la tabella (formata da celle contigue: senza righe o colonne vuote) al cui interno si trova la cella indicata.
Nel tuo caso, poichè presumo che la tabella cominci da A1, l'istruzione corretta è :
sh1.Range("A1").CurrentRegion.Select
e questo seleziona l'intera tabella, intestazione compresa. Per cui poi devi dare uno scarto con Offset alla riga successiva e poi dare un Resize per evitare la riga vuota finale (si crea una riga vuota finale perchè tu sposti la selezione di una riga in giù per evitare l'intestazione e quindi ti ritrovi con una riga vuota che non ti serve).
scusa se insisto
Devi insistere, è obbligatorio 🙂
No, nell'istruzione che riporti non c'è alcun errore, e ti restituisce in uCol il numero dell'ultima colonna valorizzata (se il range va da A1 a F500, uCol vale 6 che corrisponde a F).
In alternativa a tutta la pappardella fatta sopra, prova così:
sh1.Range(sh1.Cells(2, 1), sh1.Cells(sh1.Range("A1").CurrentRegion.Rows.Count, uCol)).Copy
se io utilizzassi uCol, non mi risulta più semplice?
adesso mi sorge una domanda, uCol, scritto come l'ho scritto io, mi è utile per copiare la tabella senza intestazioni? perchè mi sono fissato con uCol hahah
così funziona ma copia 2 righe vuote in più, devo usare offset? Si può usare un numero negativo come all'interno di offset?
copia 2 righe vuote in più
Piuttosto curioso, perchè
sh1.Range("A1").CurrentRegion.Rows.Count
non fa altro che recuperare il conteggio delle righe della regione corrente, la quale si estende da A1 fino alla prima colonna e alla prima riga vuota.Comunque certo che puoi usare uCol, basta valorizzarlo nel modo giusto, come l'hai messo tu
uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
viene valorizzato all'ultima colonna piena della prima riga; se la tabella è contigua questo è corretto.
Si può usare un numero negativo come all'interno di offset?
Sì, certo e significa "spostati sopra [se metti righe negative] o a sinistra [e metti colonne negative]"
range("C10").offset(-1, -2).select
seleziona la cella A9.
Buongiorno,
ho provato a inserire il comando offset all' interno di questa riga di codice, per evitare che mi copia le 2 righe vuote.
sh1.Range(sh1.Cells(2, 1), sh1.Cells(sh1.Range("A1").CurrentRegion.Rows.Count, uCol)).Copy
il problema è che mi da errore..
Però non fai vedere come hai scritto l'istruzione (per dirti come invece andrebbe scritta) nè dici quale errore ottieni 🙂
Set sh1 = wk1.Worksheets("Foglio1") Set sh2 = wk2.Worksheets("Raccolta Dati") ur = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1 uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column sh1.Range(sh1.Cells(2, 1), sh1.Cells(sh1.Range("A1").CurrentRegion.Rows.Count, uCol)).Copy sh2.Cells(ur, 1).PasteSpecial xlPasteValues
Utilizzare .Offset era un'alternativa. Se lo scrivi così non dovresti avere problemi.
perfetto, adesso il codice funziona. grazie mille!!
Sub CopiaRete() Dim WK1 As Workbook, WK2 As Workbook Dim sh1 As Worksheet, sh2 As Worksheet Dim uCol As Integer, Ur As Integer Application.ScreenUpdating = False Set WK1 = ThisWorkbook Set WK2 = Workbooks.Open("\\FILESERVER01\DiscoUTec\UfficioAssistenza\Hotliner\Assistenze_Rete.xlsm") Set sh1 = WK1.Worksheets("Foglio1") Set sh2 = WK2.Worksheets("Raccolta Dati") uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column Ur = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1 sh1.Range(sh1.Cells(2, 1), sh1.Cells(sh1.Range("A1").CurrentRegion.Rows.Count, uCol)).Copy sh2.Cells(Ur, 1).PasteSpecial xlPasteValues Application.CutCopyMode = False WK2.Save WK2.Close Application.ScreenUpdating = True Set sh2 = Nothing Set sh1 = Nothing Set WK1 = Nothing Set WK2 = Nothing End Sub
Un'ultima cosa, se io volessi eliminare i valori presenti solo su determinate colonne, ma lasciando invariata l'intestazione della tabella? purtoppo ho trovato solo codici che mi eliminano la colonna intera..
per esempio dovre eliminare i valori (e non l'Intestazione) delle colonne: A;B;E;F;G;H;I;J;K;P;Q
io ho fatto così
sh1.Range("A2:A65000").Select Selection.ClearContents sh1.Range("B2:B65000").Select Selection.ClearContents sh1.Range("E2:E65000").Select Selection.ClearContents sh1.Range("F2:F65000").Select Selection.ClearContents sh1.Range("G2:G65000").Select Selection.ClearContents sh1.Range("H2:H65000").Select Selection.ClearContents sh1.Range("I2:I65000").Select Selection.ClearContents sh1.Range("J2:J65000").Select Selection.ClearContents sh1.Range("K2:K65000").Select Selection.ClearContents sh1.Range("P2:P65000").Select Selection.ClearContents sh1.Range("Q2:Q65000").Select Selection.ClearContents
Grazie in anticipo
Pur avendo visto la tua richiesta, non sono potuto intervenire efficacemente.
Quindi mi hai anticipato: e hai risolto. Direi molto bene, visto che è corretto
Ora però, vedi che ci sono margini di miglioramento per ottimizzare il tuo codice (si nota che tutti quei Select e tutti quei Selecion.ClearContents hanno o possono avere qualcosa in comune che li può, in qualche modo, semplificare).
Suggerimento: generalmente non è mai necessario preselezionare un range per farci qualcosa. Puoi fare qualcosa senza Select: sh1.Range("A2:A65000").ClearContents.
Suggerimento 2: poichè hai una serie di range ben determinati, puoi utilizzare un loop (un ciclo For ... Next) per passare in rassegna i range che ti servono, e ad ogni passaggio operare il metodo desiderato (qui la parola "metodo" è in senso tecnico: un metodo di un oggetto è una sua funzione richiamabile, nell'esempio .ClearContents) .
Premesso che quello che hai scritto funziona e va bene, se hai tempo e voglia ti invito a riprogettare questo corpo di istruzioni per ottimizzarlo in, diciamo, tre linee di codice.
-
AutoreArticoli