如何从另一个工作簿复制相应的单元格值？

Question

我想将"Hacked“工作簿中的特定颜色的单元格复制到”官方“工作簿中。我还想遍历多张床单。现在我只在一张纸上进行测试，循环已经被卡住了。

Sub CopyBasel2()

Dim Hacked As Workbook
Set Hacked = Workbooks.Open("H:\BASEL Reporting - Oliver's Mock\Report Submission\BASEL2_0262CRT30062021G (Password Breaker).xls")

Dim Official As Workbook
Set Official = Workbooks.Open("H:\BASEL Reporting - Oliver's Mock\Report Submission\BASEL2_0262CRT30062021G.xls")

Dim Cell As Range

For Each Cell In Hacked.Sheets("SA-CR.1(CE)").UsedRange.Cells

If Cell.Interior.Color = 13434828 Then
Official.Sheets("SA-CR.1(CE)").Range(Cell.Address).Value = Cell.Value

End If
Next Cell

Debug.Print Hacked.Sheets("SA-CR.1(CE)").Range("C10").Interior.Color

End Sub

Answer 1

感谢大家的指导，我已经设法让我的代码按照下面的方式工作，并通过一系列的工作表完成了一个循环。

我以前的代码不能工作的原因是我同时打开了“正式”文件。当我关闭它并运行我的代码时，它运行得很平稳。有人知道背后的逻辑吗？

另外，如果有人有一种更好/更优雅的方法来执行数组和循环部分，请随意分享它。

Sub CopyBasel2()

Dim Hacked As Workbook
Set Hacked = Workbooks.Open("H:\BASEL Reporting - Oliver's Mock\Report Submission\BASEL2_0262CRT30062021G (Password Breaker).xls")

Dim Official As Workbook
Set Official = Workbooks.Open("H:\BASEL Reporting - Oliver's Mock\Report Submission\BASEL2_0262CRT30062021G.xls")

With Hacked
Set WSArray = .Sheets(Array("SA-CR.1(CE)", "SA-CR.2(CRM.1)", "SA-CR.3(CRM.2)", "SA-CR.4(RWA)", _
                  "SA-CR.6(OBS)", "SA-CR.6.1(CD)", "SA-CR.7(Recon)"))
End With

For Each ws In WSArray
For Each Cell In Hacked.Sheets(ws.Name).UsedRange

If Cell.Interior.Color = 13434828 Then
Official.Sheets(ws.Name).Range(Cell.Address).Value = Cell.Value

End If
Next Cell
Next ws

End Sub