My VBA sensei has left for pastures new and on the 1st day I am having a problem "Unable to set the Close property of the workbook class"

[RockandGrohl]

Have the below code to simply create a new tab, then open a new workbook, switch between the two tabs and then close the workbook.

' Add Price Panel information to Temp sheet.

Sheets.Add.Name = "Temp"
Set Temp = Worksheets("Temp")


Temp.Activate
Range("A1").Activate


Set PP = Workbooks.Open("H:\Sales\Price Panels\Price Panels 2019.xlsm", ReadOnly:=True)


    Dim Lastrow As Long


    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("A3").Activate
    Range("A2:B" & Lastrow).Copy
    Temp.Activate
    Range("A1").PasteSpecial xlPasteValues
    PP.Activate
    Range("D2:D" & Lastrow).Copy
    Temp.Activate
    Range("C1").PasteSpecial xlPasteValues
    PP.Activate
    Range("G2:G" & Lastrow).Copy
    Temp.Activate
    Range("D1").PasteSpecial xlPasteValues
    PP.Activate
    Range("M2:M" & Lastrow).Copy
    Temp.Activate
    Range("E1").PasteSpecial xlPasteValues


PP.Close = False

Here's what's weird - when I step through the code with F8, it all goes well until "Set PP = Workbooks.Open" blah blah blah, from there, it seems to just run through the entire code up till the end - PP.Close = False, which is where I get the error

"Unable to set the Close property of the workbook class"

From googling this, I can't see anyone with the same issue, which is really strange.


The code is more or less lifted from other workbooks which happily work.

The copy+paste functionality seems to work very well, I can see it has the correct values included.

 

[steve the fish]

Delete the = sign

 

[RockandGrohl]

Delete the = sign

Steve the Sensei! Thanks.

 

[RockandGrohl]

Delete the = sign

Hi Steve,

Do you have any idea why the Macro resumes when I step through? As soon as I get to "set PP = Workbooks.open" and press F8 on it, it just runs through the rest of the Macro"

Cheers.

 

[gallen]

Code resuming after workbook.open is just one of those (annoying) things. You will need to add a stop on the very next line to continue stepping through.