excel - 引用打开的 Excel 工作簿

Question

对 Excel 中的 VBA 来说相当新，并且在尝试引用已打开的工作簿时遇到问题。下面的代码运行良好，直到我到达尝试激活 ProductionBook 的行。我在这里收到“下标超出范围”错误，这让我相信工作簿没有被激活。关于如何克服这个问题的任何想法？提前致谢。

Sub UpdateSales()

'---Code written in the open workbook defined as ProductionBook

Dim ProductionBook As Workbook 'Master file
Dim sbSolvents As Workbook 'S&P BW-query workbook
Dim path1 As String

'---------Define path directory for S&P workbook-------------
path1 = "C:\Users\scullycs\Desktop\P&O\BW Files\Shipped & Pending\January\ISOP.xlsm"

Set sbSolvents = Workbooks.Open(path1)

With sbSolvents
    Range("j19:j36").Select
    Selection.Copy
End With

Set ProductionBook = Workbooks("Master Production File.xlsm")

With ProductionBook
    Worksheets("S&OP Progress").Range("F11").PasteSpecial (xlPasteValues) 'this is where I get the subscript error
End With

End Sub

Answer 1

试试下面的代码(在从底部算起的第 3 行，您需要将“Sheet1”修改为要从中复制的 sbSolvents 中的工作表)。

Sub UpdateSales()

'---Code written in the open workbook defined as ProductionBook

Dim ProductionBook As Workbook 'Master file
Dim sbSolvents As Workbook 'S&P BW-query workbook
Dim path1 As String

'---------Define path directory for S&P workbook-------------
path1 = "C:\Users\scullycs\Desktop\P&O\BW Files\Shipped & Pending\January\ISOP.xlsm"

Set sbSolvents = Workbooks.Open(path1)
Set ProductionBook = Workbooks("Master Production File.xlsm")

' modify "Sheet1" in the line below to your sheet name
sbSolvents.Worksheets("Sheet1").Range("J19:J36").Copy
ProductionBook.Worksheets("S&OP Progress").Range("F11").PasteSpecial xlPasteValues

End Sub