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, Dec 21 '18 at 19:00
I never use copy/paste, but does copy/paste mode disable when you `.ClearContents`? — K.Dᴀᴠɪs, Dec 21 '18 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, Dec 21 '18 at 20:51

urdearboy · Answer 1 · 2018-12-21T21:34:50.407

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 '18 at 21:34

answered Dec 21 '18 at 19:40

urdearboy

14,439
5
28
58

I still run into the following error with the above: Run-time error '1004': Method 'Range' of object'_Global' failed – J.M. Dec 21 '18 at 20:45
Seems to fail on the last line, and I can't figure out why. Any ideas as to why this error occurs? – J.M. Dec 21 '18 at 20:48
Oh. Change the last line to wb.close – urdearboy Dec 21 '18 at 20:52
Plz accept solution - we can’t put “thank yous” on our resumes :/ – urdearboy Dec 21 '18 at 22:23

score 0 · Answer 2 · answered Dec 21 '18 at 22:22

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

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

2 Answers2