copy from a closed Workbook error

[KlausW]

Hi every one

I'm trying to use this VBA code I found here in the Forum. To copy from a closed Workbook.

But it gets this error message "Object doesn't support this property or method (Error 438)", it could be because the data I want to copy from a closed Workbook has formulas in it. If that's the case, then there are some can help solve the challenge.

Any help will be appreciated

Best Regards

Klaus W

Sub SkibsNr_Rektangelafrundedehjørner1_Klik()

' Defines variables

Dim wb1 As Workbook, wb2 As Workbook



' Disable screen updating to reduce screen flicker

Application.ScreenUpdating = False



' Define which workbook is which

Set wb1 = ThisWorkbook

Set wb2 = Workbooks.Open("D:\DNBR 2024\Ankomstseddel\Ankomst sedler fastbesætning og VPL\Adresse fastbesætning og VPL.xlsm")



' Copy range A1:W70 from the Data sheet of wb2

wb2.Sheets("SkibsNr").Range("B2:J61").Copy

' Paste the copied data to range A1 of the Data sheet in wb1

wb1.Sheets("SkibsNr").Range("b2").Paste



' Close wb2

wb2.Close



End Sub

 

[MARK858]

What happens if you change

wb2.Sheets("SkibsNr").Range("B2:J61").Copy

' Paste the copied data to range A1 of the Data sheet in wb1

wb1.Sheets("SkibsNr").Range("b2").Paste

to

wb2.Sheets("SkibsNr").Range("B2:J61").Copy wb1.Sheets("SkibsNr").Range("B2")

 

[KlausW]

What happens if you change

wb2.Sheets("SkibsNr").Range("B2:J61").Copy

' Paste the copied data to range A1 of the Data sheet in wb1

wb1.Sheets("SkibsNr").Range("b2").Paste

to

wb2.Sheets("SkibsNr").Range("B2:J61").Copy wb1.Sheets("SkibsNr").Range("B2")

Hi MARK 858 it worked, it asks if I want to save the closed workbook, but I can't remember how to get Excel to skip it so it just closes by itself. Can it copy so its only Value not formula? Klaus W

 

[MARK858]

Hi MARK 858 it worked

You're welcome

it asks if I want to save the closed workbook, but I can't remember how to get Excel to skip it

Application.DisplayAlerts = False
wb2.Close False
Application.DisplayAlerts = True

Although the DisplayAlerts part is probably overkill

 

[RoryA]

Can it copy so its only Value not formula?

Yes:

wb2.Sheets("SkibsNr").Range("B2:J61").Copy
' Paste the copied data to range A1 of the Data sheet in wb1
wb1.Sheets("SkibsNr").Range("b2").PasteSpecial xlPasteValues

 

[MARK858]

Thanks @RoryA missed that part

 

[KlausW]

Yes:

wb2.Sheets("SkibsNr").Range("B2:J61").Copy
' Paste the copied data to range A1 of the Data sheet in wb1
wb1.Sheets("SkibsNr").Range("b2").PasteSpecial xlPasteValues

Hi everyone, thank you very much for your help. Just as it should be. Good day. Best Regards Klaus W