CODEWITHSUNDEEP
excelvba
Mturks83
Mturks83

Reputation: 89

Declaring a Workbook variable

VBA is returning a

run-time 13 error

when I declare a workbook as a variable & then reference variable to copy data

Sub CopyPnLData()

Dim PnLMonth As Workbook
Dim FilePath1 As String
FilePath1 = "C:\Users\mturkenburg\WHISPIR\Gareth Edlin - FP&A\Anaplan\P&L\Monthly Downloads\1.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

Upvotes: 0

Views: 50

Answers (1)

braX
braX

Reputation: 11755

I have modified your code and added some comments for your reference.

Sub CopyPnLData()
  Dim PnLMonth As WorkBook
  Dim FilePath1 As String

  FilePath1 = "C:\Users\mturkenburg\WHISPIR\Gareth Edlin - FP&A\Anaplan\P&L\Monthly Downloads\1.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

Upvotes: 1

Related Questions