我有以下代碼：
Sub CopySheet()
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = Workbooks("TestData.xlsm")
Set wb2 = Workbooks("AutoBE.xlsm")
wb1.Sheets("Sheet1").Copy After:=wb2.Sheets("Sheet1")
End Sub
這設法將 Sheet1 從 TestData 復制到 AutoBE 作業簿中。但是，由于Copy After
有沒有辦法將資料插入到 AutoBE 中的現有作業表中，例如 Sheet1 本身，為簡單起見假設它是空的？我在網上和論壇上到處找，沒有運氣。這甚至可能嗎？憑借使用 python 的經驗，我覺得它必須如此。
或者有沒有辦法像這段代碼一樣在 AutoBE 中創建這個新作業表，然后洗掉 AutoBE 中除了新創建的作業表之外的所有其他作業表？
uj5u.com熱心網友回復：
“替換”作業表
- 這是一個簡單的示例，說明您可以如何進行。
- 如果這對您不起作用，請分享其中的問題，以便我們改進。
快速修復
Sub CopyWorksheet()
' Source
Dim swb As Workbook: Set swb = Workbooks("TestData.xlsm")
Dim sws As Worksheet: Set sws = swb.Worksheets("Sheet1")
' Destination
Dim dwb As Workbook: Set dwb = Workbooks("AutoBE.xlsm")
' Reference the old worksheet...
Dim dwsOld As Worksheet: Set dwsOld = dwb.Worksheets("Sheet1")
' and store its name and its code name in variables.
Dim dName As String: dName = dwsOld.Name
Dim dCodeName As String: dCodeName = dwsOld.CodeName
' Copy.
sws.Copy After:=dwsOld
' Reference the new (copied) worksheet.
Dim dwsNew As Worksheet: Set dwsNew = dwsOld.Next
' Delete the old worksheet.
Application.DisplayAlerts = False ' delete without confirmation
dwsOld.Delete
Application.DisplayAlerts = True
' Rename the new worksheet and restore the old code name.
dwsNew.Name = dName
dwb.VBProject.VBComponents(dwsNew.CodeName).Name = dCodeName
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/528682.html
標籤：擅长vba