Macro to copy range from CSV to excel error

Question

Can you please correct the below code where I am able to execute the first part but then getting error for the second and third part.Need to copy range from csv to excel. I am able to copy from B2:B10 from CSv to A2 in excel but C2:C10 and D2:D10 is failing.

Sub Copy_Paste()
Const csvFile = "C:pathname\filename.csv"
Dim ws As Worksheet, csv As Workbook

Set ws = ThisWorkbook.Sheets("Sheet2")
Workbooks.Open csvFile

'Copying B2:B10 from CSV file to A2 in xl file
Set csv = ActiveWorkbook
cName = csv.Name
ActiveSheet.Range("B2:B10").Copy
ws.Activate
ws.Range("A2").PasteSpecial xlPasteValues

'Copying C2:C10 from CSV file to G2 in xl file
Set csv = ActiveWorkbook
cName = csv.Name
ActiveSheet.Range("C2:C10").Copy
ws.Activate
ws.Range("G2").PasteSpecial xlPasteValues

'Copying D2:D10 from CSV file to Y2 in xl file
Set csv = ActiveWorkbook
cName = csv.Name
ActiveSheet.Range("D2:D10").Copy
ws.Activate
ws.Range("Y2").PasteSpecial xlPasteValues


End Sub

Answer 1

when pasting just values it is quicker to simply equate the two ranges.

Sub Copy_Paste()

Const csvFile = "C:pathname\filename.csv"

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2")

Dim csv As Workbook
Set csv = Workbooks.Open(csvFile)

Dim csvws As Worksheet
Set csvws = csv.Worksheets(1)

ws.Range("A2:C10").Value = csvws.Range("B2:D10").Value

End Sub

---

If your ranges are not congruent then use multiple lines, one for each congruent area:

Sub Copy_Paste()

Const csvFile = "C:pathname\filename.csv"

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2")

Dim csv As Workbook
Set csv = Workbooks.Open(csvFile)

Dim csvws As Worksheet
Set csvws = csv.Worksheets(1)

ws.Range("A2:A10").Value = csvws.Range("B2:B10").Value
ws.Range("B2:B10").Value = csvws.Range("C2:C10").Value
ws.Range("C2:C10").Value = csvws.Range("D2:D10").Value

End Sub