我卡住了，我正在嘗試撰寫宏，我們有 2 個檔案；ORG - 我從 NOT_ORG 運行宏的空檔案 - 帶有一些資料的作業表我想在 NOT_Org 中遍歷作業表，檢查此作業表名稱是否在陣列中，如果是，請在 ORG 檔案中創建一個新作業表，粘貼來自NOT_ORG，并從 NOT_ORG 回圈下一張紙以檢查相同。到目前為止，在我撰寫的代碼下方，我對如何在作業簿之間輕松切換感到困惑，因為我遇到了錯誤 - 當我設定 WB2 并且第二個問題在 For each 回圈中時。有人可以指出我正確的方向，如何在每個回圈中在這些作業簿之間切換？
Sub Test1()
Dim WshtNames As Variant
Dim WshtNameCrnt As Variant
Dim ws As Worksheet
Dim os As Worksheet
Dim x As String
Dim WB1 As Workbook
Dim WB2 As Workbook
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open("C:\NOT_ORG.xlsx")
' loop through sheets in file that i just open (wb2), check if sheet name is in array below,
' copy A1 value & assign to a variable sheet name that i am copying from, with new prefix
' _new / open "org file" - that I am running macro from - create a new sheet and assign this
' sheet name, paste the value. And do the same for each sheet if this sheet name is in list.
'
WshtNames = Array("2", "3")
For Each WshtNameCrnt In WshtNames
With Worksheets(WshtNameCrnt).Activate
Range("A1").Select
Selection.Copy
Sheets.Add.Name = WshtNameCrnt & "_new"
' -> S
'x = ActiveSheet.Name
'#MsgBox (x)
ActiveSheet.Paste
End With
Next WshtNameCrnt
End Sub
謝謝 eM
uj5u.com熱心網友回復：
請更換：
For Each WshtNameCrnt In WshtNames
With Worksheets(WshtNameCrnt).Activate
Range("A1").Select
Selection.Copy
Sheets.Add.Name = WshtNameCrnt & "_new"
' -> S
'x = ActiveSheet.Name
'#MsgBox (x)
ActiveSheet.Paste
End With
Next WshtNameCrnt
和：
For Each WshtNameCrnt In WshtNames
WB1.Sheets.Add.Name = WshtNameCrnt & "_new"
WB2.Worksheets(WshtNameCrnt).Range("A1").Copy ActiveSheet.Range("A1")
Next WshtNameCrnt
為了作業，名為“1”和“2”（不是它們的索引）的兩個作業表應該存在于WB2.
激活、選擇只消耗Excel資源，讓代碼變慢，并沒有帶來任何好處……
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/346667.html
標籤：vba