VBA Run-time error 438 appears when “paste” runs

Question

I'm having an issue with the above: I am using the answer provided, but still hitting an object error. Can you see what i'm missing? I hit the errror at "Cash_Sheet.Range("C8").PasteSpecial xlPasteValues"

`Sub Refresh_Cash()      
Dim Morning_Export As Workbook     
Dim Cash_Sheet As Worksheet  

'Open MorningExport cash workbook     
Set Morning_Export = Workbooks.Open(Range("varMornExpPath"))  

'Copy cash from Morning_Export_Settlement_Cas tab: 
Morning_Export.Sheets("Morning_Export_Settlement Cas").Range("A1:AR5000").Copy  

'Set the sheet in this file to paste to:
Set Cash_Sheet = ThisWorkbook.Worksheets("Cash")  
'Clear prior data from EOD_Check
 Cash_Sheet.Range("rngRefreshPFMExp").ClearContents  

'EVERYTHING WORKS UP UNTIL THIS POINT BUT THEN FAILS HERE
 Cash_Sheet.Range("C8").PasteSpecial xlPasteValues  

'Close MorningExport book:     
Morning_Export.Close  

End Sub

two things: A) ensure the source/destination ranges are the same size as a good practice despite it not always being required, and B) try clearing contents before copying, so you have a copy/paste on subsequent lines. — Cyril
– Cyril, Commented Dec 21, 2018 at 19:00
I never use copy/paste, but does copy/paste mode disable when you .ClearContents? — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Dec 21, 2018 at 19:00
K. Davis and urdearboy are right...running a ClearContents operation after your Copy operation clears the clipboard and there's nothing to paste, thus your error. — MBB70
– MBB70, Commented Dec 21, 2018 at 20:51

urdearboy · Accepted Answer · 2018-12-21 21:34:50Z

1

Sub Refresh_Cash()

Dim wb As Workbook: Set wb = Workbooks.Open(Range("varMornExpPath"))
Dim cs As Worksheet: Set cs = ThisWorkbook.Sheets("Cash")

cs.Range("rngRefreshPFMExp").ClearContents
wb.Sheets("Morning_Export_Settlement Cas").Range("A1:AR5000").Copy
cs.Range("C8").PasteSpecial xlPasteValues

wb.Close

End Sub

edited Dec 21, 2018 at 21:34

answered Dec 21, 2018 at 19:40

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

J.M. Over a year ago

I still run into the following error with the above: Run-time error '1004': Method 'Range' of object'_Global' failed

J.M. Over a year ago

Seems to fail on the last line, and I can't figure out why. Any ideas as to why this error occurs?

urdearboy Over a year ago

Oh. Change the last line to wb.close

urdearboy Over a year ago

Plz accept solution - we can’t put “thank yous” on our resumes :/

HackSlash · Accepted Answer · 2018-12-21 22:22:35Z

Instead of using copy\paste you can directly write the values from one range in to another. This works much faster on large data sets because it doesn't have to copy twice. It also results in cleaner code.

Public Sub Refresh_Cash()
    Dim Morning_Export As Workbook
    Dim Cash_Sheet As Worksheet

    'Open MorningExport cash workbook
    Set Morning_Export = Workbooks.Open(ActiveSheet.Range("varMornExpPath"))

    'Set the sheet in this file to paste to:
    Set Cash_Sheet = ThisWorkbook.Worksheets("Cash")

    ' Set the values directly
    Cash_Sheet.Range("C8") = Morning_Export.Sheets("Morning_Export_Settlement Cas").Range("A1:AR5000")

    'Close MorningExport book:
    Morning_Export.Close
End Sub

SEE: Copy/PasteSpecial vs Range.Value = Range.Value

Collectives™ on Stack Overflow

VBA Run-time error 438 appears when “paste” runs

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related