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

 

[kylemr]

NOP
Should not be

Sorry I dont understand - does that mean it should not be an issue?

 

[mohadin]

Try

kylemr.xlsm

Shared with Dropbox

www.dropbox.com

 

[kylemr]

Im now getting a runtime error, when I debug the line below is highlighted yellow:

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]

Thanks for your script. Error message now says script out of range

 

[mohadin]

REplace with

  .Sheets("Sheet1").Range("A4").Resize(UBound(a), UBound(a, 2)) = a

an extra s deleted

 

[kylemr]

REplace with

  .Sheets("Sheet1").Range("A4").Resize(UBound(a), UBound(a, 2)) = a

an extra s deleted

That has worked! You're a legend! Thank you for your help!

 

[mohadin]

Oh ,Thank god
Thank you
Be happy

 

[mohadin]

kylemr.xlsm

Shared with Dropbox

www.dropbox.com