macro for saving tab as CSV

[biglb79]

is there a macro I can use that will save my current tab down to my desktop as a CSV file using the file name as the tab name?

 

[Smitty]

Have you tried recording a macro doing what you want?

Once you have the recorded code, you can use the sheet name as the filename (ActiveSheet.Name).

HTH

 

[biglb79]

I've done that before. I just didn't know you could save the new tab down onto my desktop as part of the recording process still. I tried and it's giving me an error

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A6:B6").Select
Range("B6").Activate
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Payroll JE 09.13.18").Select
Sheets("Payroll JE 09.13.18").Copy
Windows("homework for Kim.xlsm").Activate
Windows("Book3").Activate
ChDir "C:\Users\bbeachler\Desktop"
ActiveWorkbook.SaveAs Filename:="C:\Users\bbeachler\Desktop\Book3.txt", _
FileFormat:=xlText, CreateBackup:=False
End Sub

 

[Smitty]

What's the error?

Try:

ActiveWorkbook.SaveAs Filename:="C:\Users\bbeachler\Desktop" & ActiveSheet.Name & ".txt", _
FileFormat:=xlText, CreateBackup:=False

 

[John_w]

is there a macro I can use that will save my current tab down to my desktop as a CSV file using the file name as the tab name?

I assume you mean use the tab name as the file name. Try this:

Public Sub Save_Sheet_As_CSV()
    
    Dim fileName As String
    
    With ActiveSheet
        fileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator & .Name
        .Copy
    End With
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs fileName, FileFormat:=xlCSV
    ActiveWorkbook.Close False
    Application.DisplayAlerts = True

End Sub

 

[biglb79]

that's perfect, thanks!

I assume you mean use the tab name as the file name. Try this:

Public Sub Save_Sheet_As_CSV()
    
    Dim fileName As String
    
    With ActiveSheet
        fileName = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator & .Name
        .Copy
    End With
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs fileName, FileFormat:=xlCSV
    ActiveWorkbook.Close False
    Application.DisplayAlerts = True

End Sub