Macro to Export Sheet as CSV

[howard]

I have code to export a sheet as a csv

I will import one file at a time and selectb the macto to export sheet as a csv


I would like the csv filename to be be the same as the text in A6 followed by .csv for e.,g. WLTI.csv

I will import one file at a time , select the macro to export sheet as a csv and then import the next week

Your assistance in amending my code is most appreciated

 Sub Export_Imported_Sheet_As_CSV()

Application.DisplayAlerts = False

Dim Sht As Worksheet

Set Sht = Worksheets("Statement")

Sht.Copy

ActiveWorkbook.SaveAs Filename:="C:\extract\BRTI.CSV", FileFormat _

:=xlCSV

ActiveWorkbook.Close



Application.DisplayAlerts = True

end Sub

 

[Jimmypop]

Good day...

Try:

Sub Export_Imported_Sheet_As_CSV()
    Application.DisplayAlerts = False
    Dim Sht As Worksheet
    Set Sht = Worksheets("Statement")
    Sht.Copy
    ActiveWorkbook.SaveAs Filename:="C:\extract\" & Range("A6").Value & ".csv", FileFormat _
    :=xlCSV
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

 

[howard]

Many thanks for the help. Your code works perfectly

 

[Jimmypop]

Glad to help.

 

[howard]

Hi Jimmypop

i need one more change. I want to savee the file with the name pertaining to text on sheets "Codes" in D1

D1 has a formula

i get a subscript if range

   ActiveWorkbook.SaveAs Filename:="C:\Pull\" & Sheets("Codes").Range("D1").Value & ".csv", FileFormat _
    :=xlCSV

Kindly amend my code

 

[Jimmypop]

ActiveWorkbook.SaveAs Filename:="C:\Pull\" & Sheets("Codes").Range("D1").Value & ".csv", FileFormat _ :=xlCSV

Try:

ActiveWorkbook.SaveAs Filename:="C:\Pull\" & Worksheets("Codes").Range("D1").Value & ".csv", FileFormat _
    :=xlCSV

 

[howard]

Unfortunately I still get Subscript out of range

Please check & advise

 

[Jimmypop]

Mmmmm...ok try following approach: (Hope I get it right seeing as I also still learning):

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = wb.Codes ' you can change this to reference the sheet where your save name lives.
    SaveName = ws.Range("D1") 'Change this to wherever you want to pull the save name
    ActiveWorkbook.SaveAs Filename:="C:\Pull\" _
    & SaveName & ".csv", FileFormat _
    :=xlCSV
End Sub

 

[Jimmypop]

Just wait before you try above...

 

[Jimmypop]

Unfortunately I still get Subscript out of range

Please check & advise

Please ignore:

Mmmmm...ok try following approach: (Hope I get it right seeing as I also still learning):

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = wb.Codes ' you can change this to reference the sheet where your save name lives.
    SaveName = ws.Range("D1") 'Change this to wherever you want to pull the save name
    ActiveWorkbook.SaveAs Filename:="C:\Pull\" _
    & SaveName & ".csv", FileFormat _
    :=xlCSV
End Sub

And use:

Private Sub CommandButton1_Click()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Codes") ' you can change this to reference the sheet where your save name lives.
    SaveName = ws.Range("D1") 'Change this to wherever you want to pull the save name
    ActiveWorkbook.SaveAs Filename:="C:\Pull\" _
    & SaveName & ".csv", FileFormat _
    :=xlCSV
End Sub