Open CSV and copy

Question

Friends, I try to open a CSV file (every day generated from another program) every day and copy the data in a CSV sheet to a specific sheet in my current book. I have been working on this code for some time, and I think it is really close to being right, but I continue to receive error 438 at runtime on my copy / paste line. Any help?

Thanks!

Here is my code:

Sub GetCSV() Dim thatWB As Workbook, thisWB As Workbook Dim thisWS As Worksheet, thatWS As Worksheet Dim zOpenFileName As String Dim inputData As String 'get name of sheet to open inputData = InputBox("Enter name of file") 'open CSV file zOpenFileName = Application.GetOpenFilename 'error handling If zOpenFileName = "" Then Exit Sub Application.ScreenUpdating = False Set thisWB = ThisWorkbook 'destination workbook Set thisWS = Sheets("f_dump") 'destination worksheet Set thatWB = Workbooks.Open(zOpenFileName) 'source CSV Set thatWS = thatWB.Sheets(inputData) 'source worksheet Application.CutCopyMode = False thatWB.thatWS.Range("A1:G150").Copy Destination:=thisWB.thisWS.Range("A1") thatWB.Close End Sub 

Answer 1

Try to look at it. I removed thisWB and ThatWB from your copy and pasted part of the code because this was the source of the first problem (and I moved the book spec to the sheet declaration).

Then the next issue was with Paste. I'm not sure why, but when calling a range you need to use PasteSpecial (VBA in excel is a bit of magic, not programming / scripting)

 Sub GetCSV() Dim thatWB As Workbook, thisWB As Workbook Dim thisWS As Worksheet, thatWS As Worksheet Dim zOpenFileName As String Dim inputData As String 'get name of sheet to open inputData = InputBox("Enter name of file") 'open CSV file zOpenFileName = Application.GetOpenFilename 'error handling If zOpenFileName = "" Then Exit Sub Application.ScreenUpdating = False Set thisWB = ThisWorkbook 'destination workbook Set thisWS = ThisWorkbook.Sheets("Sheet1") 'destination worksheet Set thatWB = Workbooks.Open(zOpenFileName) 'source CSV Set thatWS = thatWB.Sheets(inputData) 'source worksheet Application.CutCopyMode = False thatWS.Range("A1:G150").Copy thisWS.Range("A1:G150").PasteSpecial xlPasteAll thatWB.Close End Sub