Runtime Error 438

[scottiedawg4]

I am trying to import a range from a CSV file into an Excel worksheet.

Code:
'Prompt user for location of alltxt data file
openAlltxt = Application.GetOpenFilename(Title:="Locate alltxt File", MultiSelect:=False)
If openAlltxt = "False" Then
Exit Sub 'exit if they press Cancel
Else
Dim WB As Workbook
Dim Alltxt As Workbook
Set WB = ActiveWorkbook
Set Alltxt = Application.Workbooks.Open(openAlltxt)
Alltxt.ActiveSheet.Range("A1:F200000").Copy
ActiveWorkbook.ActiveWorksheet.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "General"
On Error Resume Next
Alltxt.Close False

End If
*End Code

I get the runtime error for the "ActiveWorkbook.ActiveWorksheet" line

Any thoughts?

Thanks

 

[VoG]

Maybe just

Range("A1").PasteSpecial Paste:=xlPasteValues

 

[scottiedawg4]

That gets rid of the runtime error. However, while the data gets copied correctly from the CSV file, it does not appear in the Excel worksheet.

 

[VoG]

Maybe it should be like this

Range("A1:F200000").Copy
ThisWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[scottiedawg4]

That gets an error "subscript out of range"

 

[VoG]

You need to change Sheet1 to the actual name of the sheet.

 

[scottiedawg4]

Fixed. Thank you all.