声明工作簿变量

Question

VBA 返回一个

运行时 13 错误

当我将工作簿声明为变量然后引用变量以复制数据时

Sub CopyPnLData()
Dim PnLMonth As Workbook
Dim FilePath1 As String
FilePath1 = "C:UsersmturkenburgWHISPIRGareth Edlin - FP&AAnaplanP&LMonthly Downloads1.1 P&L OPEX Buckets - Month.CSV"
Workbooks.Open (FilePath1)
Set PnLMonth = Application.Workbooks("1.1 P&L OPEX Buckets - Month.CSV")
Workbooks(PnLMonth).Worksheets("Sheet 1").Range("A1").CurrentRegion.Copy
ThisWorkbook.Worksheets("1.AP Data - P&L").Range("c1").Paste
End Sub

Answer 1

我已经修改了您的代码并添加了一些注释供您参考。

Sub CopyPnLData()
Dim PnLMonth As WorkBook
Dim FilePath1 As String
FilePath1 = "C:UsersmturkenburgWHISPIRGareth Edlin - FP&AAnaplanP&LMonthly Downloads1.1 P&L OPEX Buckets - Month.CSV"
' check the variable first
If Dir(FilePath1) = "" Then
MsgBox "File not found. : " & FilePath1
Exit Sub
End If
' set your workbook variable when you open it like this
Set PnLMonth = Workbooks.Open(FilePath1)
' use your workbook variable in code like this
PnLMonth.Worksheets("Sheet 1").Range("A1").CurrentRegion.Copy
' not sure if this line is going to work, as you did not provide details about it
ThisWorkbook.Worksheets("1.AP Data - P&L").Range("c1").Paste
' close it when you are done
PnLMonth.Close False
End Sub