VBA Export Range to CSV Help

[kylemr]

Hi,

I am trying to save a range from a specific worksheet within a macro enabled workbook. I would like to export the range, leaving all data intact after saving. I would also like to save the csv in the same location as the workbook.

I've got this far on my VBA script so far, can anyone please assist? It's not working at all

Sub SaveAsCSV()
   
    Dim wb As Workbook
    Dim nam As String
   
    Application.DisplayAlerts = False
    With Worksheets("Export")
        nam = .Range("A1")
        .Range("A4:T1000").Copy
    End With
    Set wb = Workbooks.Add
    With wb
    ActiveSheet.PasteSpecial
   
    Sub ExampleToSaveWithSamePathDifferentName()
    Dim sFilename As String
    sFilename = "WorkbookName.xls" 'You can give a nem to save

    Workbooks.Add
    'Saving the Workbook
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sFilename

    End Sub

 

[mohadin]

Try

Sub SaveAsCSV()
    Dim wb As Workbook
    Dim nam As String
    Application.DisplayAlerts = False
    With Worksheets("Export")
        nam = .Range("A1")
        .Range("A4:T1000").Copy
    End With
    Set wb = Workbooks.Add
    With wb
        .SaveAs Filename:=ThisWorkbook.Path & "\" & nam, FileFormat:=xlCSV, CreateBackup:=True
        .Close
    End With
    ThisWorkbook.Close False
End Sub

 

[kylemr]

Try

Sub SaveAsCSV()
    Dim wb As Workbook
    Dim nam As String
    Application.DisplayAlerts = False
    With Worksheets("Export")
        nam = .Range("A1")
        .Range("A4:T1000").Copy
    End With
    Set wb = Workbooks.Add
    With wb
        .SaveAs Filename:=ThisWorkbook.Path & "\" & nam, FileFormat:=xlCSV, CreateBackup:=True
        .Close
    End With
    ThisWorkbook.Close False
End Sub

It returned an error that just said '400'

Any thoughts?

 

[mohadin]

Tested ok in here
gave same error when
Range("A1") in sheets("Export") is not empty

 

[kylemr]

Tested ok in here
gave same error when
Range("A1") in sheets("Export") is not empty

 

[kylemr]

Does that mean that cell A1 must be empty? or must be populated? sorry for the basic questions I am a beginner with VBA (obviously)

 

[mohadin]

Must be populated with the file name you want to save the csv file with

 

[kylemr]

Must be populated with the file name you want to save the csv file with

I put the file name DB Extract in cell A1 but it still comes up with the 400 error

 

[kylemr]

Does it matter that its a table?

 

[mohadin]

NOP
Should not be