新手來了！對于特定的作業簿或特定的作業表，我有一個我想重復的操作。
有沒有辦法在不復制和粘貼第二、第三等作業表的整個代碼的情況下做到這一點？只有作業簿和作業表名稱更改。其他操作（例如復制粘貼）保持不變。
雖然有一個“For Each 回圈”，但我不知道如何以一種允許我準確指定哪些作業表的方式來做到這一點。
例如，我是
- 第 1 步：從作業簿“Red”表“Apple”中復制資料。粘貼到輸出作業簿中。
- 重復動作。第 2 步：從作業簿“黃色”表“香蕉”中復制資料。粘貼到同一個輸出作業簿中。
如果有人可以提出建議，這是我的代碼。VBA新手在這里謝謝！
Sub CopyPastefromOtherWB()
Range("B13").Select
'Activate WB1
Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\Red"
Worksheets("Apple").Activate
Range("A1").Select
Do While Selection.Value <> "Mar"
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
'Activate output notebook
Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\OutputWB"
Worksheets("Sheet1").Activate
Range("B13").PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'HERE IS WHERE THE REPEAT HAPPENS. Activate WB2
Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\Yellow"
Worksheets("Banana").Activate
Range("A1").Select
Do While Selection.Value <> "Mar"
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
'Activate output notebook
Workbooks.Open Filename:= "C:\Users\Desktop\My macro projects\OutputWB"
Worksheets("Sheet1").Activate
Range("C13").PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
End Sub
uj5u.com熱心網友回復：
請參閱如何避免在 Excel VBA 中使用 Select。
Sub CopyPastefromOtherWB(ByVal FromPath As String, ByVal FromSheetName As String, ByVal TargetCell As Range)
With Workbooks.Open(FromPath)
With .Worksheets(FromSheetName)
Dim c As Range
Set c = .Rows(1).Find("Mar", LookAt:=xlWhole).Offset(1, 0)
TargetCell.Resize(c.Rows.Count, 1).Value = .Range(c, c.End(xlDown)).Value
End With
.Close False
End With
End Sub
With Workbooks.Open("C:\Users\Desktop\My macro projects\OutputWB").Worksheets("Sheet1")
CopyPastefromOtherWB "C:\Users\Desktop\My macro projects\Red", "Apple", .Range("B13")
CopyPastefromOtherWB "C:\Users\Desktop\My macro projects\Yellow", "Banana", .Range("C13")
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/351865.html
上一篇：如何選擇移動的范圍