Open CSV and copy

Question

I'm trying to open a CSV file (generated daily from another program) and copy the data into a certain sheet in my current workbook.

I get a run-time error 438 on my copy/paste line.

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 this. I removed thisWB and ThatWB from your copy and paste part of code, because it was source of first issue (and i moved workbook specification to sheet declaration).

Then next issue was with Paste. Im not sure why, but when calling on range, you need to use PasteSpecial (VBA in excel is a bit magic instead of programing/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