Trasferisci foglio di lavoro alla nuova cartella di lavoro

Incredibilmente novizio con excel VBA. Ho trovato questa domanda in molti luoghi diversi ma non riesco a manipolare il codice che ho cercato di lavorare. In sostanza, ho una cartella di lavoro che crea una nuova cartella di lavoro, copia interamente un foglio, salva la cartella di lavoro e quindi deve trasferire un secondo foglio dalla cartella di lavoro originale. Lo ho fatto con questo:

Wb1.Sheets("Scorecard").Range("A1:M37").Value(11) = Wb.Sheets("Scorecard").Range("A1:M37").Value(11) 

where Wb1 è il libro "trasferimento" e Wb è il "trasferimento da" cartella di lavoro. Il problema è che non si intendono hide le intestazioni / i piè di pagina, il logo (logo) o le colonne nascoste.

Ho provato questo ma sembra solo aprire un altro libro e bloccare il mio sistema e aprire un terzo libro:

 Wb.Sheets("Scorecard").Copy Wb1.Sheets("Scorecard").PasteSpecial xlPasteValues 

Mi sento come se manchi qualcosa di molto ovvio, ma sono stanco di battere la testa contro il muro su questo. Il codice completo su questo punto è sotto:

 Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Sub NewWb () Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Dim Aname come string Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Dim HospName come string Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Dim DateDone As String Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Dim xPath come string Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Dim Wb come cartella di lavoro Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Dim Wb1 come cartella di lavoro Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Impostare Wb = ThisWorkbook Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate xPath = Application.ActiveWorkbook.Path Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Aname = "CS Preventson Assessment" Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate HospName = fogli ("ospedale"). Gamma ("I8") Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate DataDone = Sostituisci ((Foglie ("ID ospedale"). Range ("I13") Testo), "/", "-") Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate ActiveWorkbook.Sheets ("Controllo della conformità") Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Con ActiveSheet.UsedRange Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate ActiveSheet.Shapes ("Button 1") Elimina Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Sheets.Add.Name = "Scorecard" Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate ActiveWorkbook.SaveAs Nome file: = xPath & "\" & HospName & "." Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate & DateDone & ".xlsx" Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate Imposta Wb1 = Opere di lavoro.Open (xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Sub NewWb() Dim Aname As String Dim HospName As String Dim DateDone As String Dim xPath As String Dim Wb As Workbook Dim Wb1 As Workbook Set Wb = ThisWorkbook xPath = Application.ActiveWorkbook.Path Aname = "CS Diversion Preventson Assessment" HospName = Sheets("Hospital ID").Range("I8") DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-") Application.ScreenUpdating = False ActiveWorkbook.Sheets("Compliance Checklist").Copy With ActiveSheet.UsedRange .Value = .Value End With ActiveSheet.Shapes("Button 1").Delete Sheets.Add.Name = "Scorecard" ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx" Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx") Wb.Activate 

Grazie in anticipo

Questo ha funzionato per me su Excel 2013, utilizzando il metodo Worksheet.Copy . Ho anche provato una mossa, utilizzando il metodo Worksheet.Move , ma ci sono voluti circa 10 secondi per spostare il foglio (!).

 Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub Public Sub copy_it_buster () Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub Dim to_copy Come foglio di lavoro, copy_after come foglio di lavoro Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub Impostare to_copy = Cartelle di lavoro ("Book2"). Fogli (1) 'O qualsiasi foglio di lavoro in cui si sta trasferendo Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub Impostare copy_after = Cartelle di lavoro ("Book3"). Fogli (1) 'O qualsiasi foglio di lavoro che si desidera copiare dopo. Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub 'Si noti che questo è in una cartella di lavoro diversa! Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub to_copy.Copy Dopo: = copy_after Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub 'to_copy.Move After: = copy_after' se non si desidera mantenerlo where Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub era originariamente Public Sub copy_it_buster() Dim to_copy As Worksheet, copy_after As Worksheet Set to_copy = Workbooks("Book2").Sheets(1) ' Or whatever worksheet you're moving from Set copy_after = Workbooks("Book3").Sheets(1) ' Or whatever worksheet you want to copy after. ' Note that this is in a different workbook! to_copy.Copy After:=copy_after 'to_copy.Move After:=copy_after ' if you don't want to keep it where ' it was originally End Sub 

Modifica 2 : dopo la copia, per convertire tutte le formule in valori piatti, prova questa cortesia del sito MVP di Ron de Bruin :

 dim sh as Worksheet set sh = ' whatever your new worksheet is With sh.UsedRange .Value = .Value End With dim sh come Foglio di lavoro dim sh as Worksheet set sh = ' whatever your new worksheet is With sh.UsedRange .Value = .Value End With set sh = 'qualunque sia il nuovo foglio di lavoro dim sh as Worksheet set sh = ' whatever your new worksheet is With sh.UsedRange .Value = .Value End With Con sh.UsedRange dim sh as Worksheet set sh = ' whatever your new worksheet is With sh.UsedRange .Value = .Value End With 

Modifica per il caso Move ho provato originariamente, quando ho iniziato con:

 Book2 Book3 Sheet1 Sheet1 Book2 Book3 Book2 Book3 Sheet1 Sheet1 

Ho finito con:

 no Book2! Book3 Sheet1 Sheet1 (2) no Book2! no Book2! Book3 Sheet1 Sheet1 (2) 

Modifica per Copy , ottengo:

 Book2 Book3 Sheet1 Sheet1 Sheet1(2) Book2 Book3 Book2 Book3 Sheet1 Sheet1 Sheet1(2) 

Tieni presente che questo richiede che la cartella di lavoro di destinazione già esista. Inoltre, se si desidera copiare / passare a un foglio di lavoro esistente anziché dopo , basta cambiare After a Before nella Copy chiamata.

E un PS : per ridurre la frustrazione di questo e di progetti futuri, utilizzare nomi come FromWorkbook e ToWorkbook piuttosto che Wb e Wb1 . Potrebbe non sembrare molto, ma nella mia esperienza riduce notevolmente il carico di lavoro mentale durante il debug!

Hai considerato di salvare / copiare la cartella di lavoro di origine (Wb) come una nuova cartella di lavoro (Wb1), quindi modificare il nuovo?

Così, invece di creare un nuovo wb vuoto e aggiungerlo … Crea una copia dell'originale, quindi rimuovi da ciò che ti serve.