Errore: Metodo 'Range' dell'oggetto '_Worksheet' non riuscito

[EdoardoPartecipante Edoardo]

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.

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Quel sh2.Range costruito così non mi piace molto, normalmente ti funziona?

[EdoardoPartecipante Edoardo]

normalmente funziona, per esempio nella riga superiore non mi da nessun errore..

sh1.Range(sh1.Cells(1, 1), sh1.Cells(1, uCol)).Copy

 

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Il codice che proponi è diverso:

sh1.range(cella, cella).copy

vs

sh2.range(cella).pastespecial

[EdoardoPartecipante Edoardo]

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.

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Edoardo ha scritto:

sh1.Range(sh1.Cells(1, 1), sh1.Cells(1, uCol)).Copy
sh2.Range(sh2.Cells(1, 1), sh2.Cells(Ur, 1)).PasteSpecial xlPasteValues

Allora, 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.

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

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 🙂

[EdoardoPartecipante Edoardo]

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

[EdoardoPartecipante Edoardo]

togliendolo mi copia comunque l'intestazione, credo ci sia qualcosa di sbagliato nella variabile uCol..

uCol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column

 

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Edoardo ha scritto:

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. 

 

[EdoardoPartecipante Edoardo]

sh1.Range("A2").CurrentRegion.Select

così mi seleziona senza intestazione? perchè mi da errore

[EdoardoPartecipante Edoardo]

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..

 

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Edoardo ha scritto:

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).

Edoardo ha scritto:

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).

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

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

[EdoardoPartecipante Edoardo]

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

[EdoardoPartecipante Edoardo]

così funziona ma copia 2 righe vuote in più, devo usare offset? Si può usare un numero negativo come all'interno di offset?

 

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Edoardo ha scritto:

copia 2 righe vuote in più

Piuttosto curioso, perchè sh1.Range("A1").CurrentRegion.Rows.Countnon 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.

Edoardo ha scritto:

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.

 

[EdoardoPartecipante Edoardo]

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..

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

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.

[EdoardoPartecipante Edoardo]

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

 

[EdoardoPartecipante Edoardo]

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

 

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

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.

[EdoardoPartecipante Edoardo]

okok perfetto. grazie mille della disponibilità!  

[Autore]

Articoli