Transfer data range from worksheet as new csv file in dedicated directory

Question

Quite new to VBA coding, I have the following data in a worksheet called "int_151_schedule", I am trying to take the data that is in the range A4:AK16 and create a new csv from this in the following directory: '\Desktop\Test'

My code is depicted below but I am getting the error ID 1004, indicating that it is not saving it to the path directory successfully. I have seen similar questions to this but haven't had much success. Is there anything wrong with my code?

Sub CreateNewCSV()


Sheets("int_151_schedule").Copy
Application.DisplayAlerts = False

With ActiveWorkbook
    
    .SaveAs Filename:="\Desktop\Test " & Range("A4:AK16").Value & ".csv"
    .Close savechanges:=True
End With

Application.DisplayAlerts = True
End Sub

score 0 · Accepted Answer · answered Sep 14 '21 at 02:34

I think your code is trying to save the file with a filename of contents of your array, which may not be what you want to do.

You also need to include the entire file path with drive. (e.g. C:\

First copy the contents of A4:AK16 to a new workbook and then save this workbook as a *.csv file.

Try

Sub CreateNewCSV()
    Range("A4:AK16").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Desktop\Test\myfilename.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
End Sub

Transfer data range from worksheet as new csv file in dedicated directory

1 Answers1