Macro saved as CSV stops Macro from working

Question

I'm working on a Macro that makes several CSV files from a certain area while applying a filter. There are two issues.

The workbook will be saved as a file-format retrieved from it's name (cell B15 and the word 'Week')
I cannot find a way to loop this Macro until Cell B15 is empty.

Can anyone help? Thanks in advance.

Example wrong format:

Sub CSVMaker()
'
' CSVMaker Macro
'

'
    ActiveSheet.Range("$A$17:$M$240000").AutoFilter Field:=2, Criteria1:="<>"
    Range("A18:B18").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add.Name = Range("A10").Value & "." & Range("A7").Value & "." & "Week" & Range("B15").Value
        ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name, FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Sheets("Input").Select
    Columns("B:B").Select
    Range("B6").Activate
    Selection.Delete Shift:=xlToLeft
End Sub

Well your filename probably should have a `.csv` file extension `Filename:=ActiveSheet.Name & ".csv"` • Please show what you have tried to loop? Also it is very unclear what exactly you want to do in each loop. • Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) to improve your question. — Pᴇʜ, Dec 07 '20 at 13:42
Hi I found the mistake. I've taken out the dots in between the name (& "." &). That works for me to. I try to loop the complete sequence until cell B15 is empty. — TGB, Dec 07 '20 at 14:12
@Well, that sentence is not enough explanation and exactly what you already wrote. You need to give a lot more information and [edit] your question accordingly. See [mcve] to understand how a good example would look like. Reading [ask] might help too. Please improve your question and give as much infomation as possible. Remember we don't know anything about your project except what you tell us. Screenshots and good examples might help too. — Pᴇʜ, Dec 07 '20 at 14:16
As of right now you don't have a loop to continue the process. Are the CSV files being created from the same worksheet? Do you have an example you can attach? — CDay, Dec 07 '20 at 19:19
Hi, the loop function I've added was a simple one: Do Until IsEmpty("B15") (...) Loop It didn't work due to the wrong format. As that issue is solved, the issue with the loop has also been solved. Thanks for the help everyone! — TGB, Dec 08 '20 at 14:04

score 0 · Answer 1 · answered Dec 08 '20 at 14:34

So finally the answer is the filename needs a correct extension like Filename:=ActiveSheet.Name & ".csv".

And I recommend to avoid .Select and do the following:

Option Explicit

Public Sub CSVMaker()
    Dim ws As Worksheet
    Set ws = ActiveSheet 'better ThisWorkbook.Worksheets("your-sheet-name")

    ws.Range("$A$17:$M$240000").AutoFilter Field:=2, Criteria1:="<>"

    ws.Range("A18:B18", ws.Range("A18:B18").End(xlDown).End(xlDown)).Copy

    Dim SheetName As String
    SheetName = ws.Range("A10").Value & "." & Range("A7").Value & "." & "Week" & Range("B15").Value

    ThisWorkbook.Sheets.Add.Name = SheetName
    ThisWorkbook.Sheets(SheetName).Paste

    Application.CutCopyMode = False
    Application.DisplayAlerts = False

    ThisWorkbook.Sheets(SheetName).Copy
    ActiveWorkbook.SaveAs Filename:=SheetName & ".csv", FileFormat:=xlCSV, CreateBackup:=True
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

    ThisWorkbook.Sheets("Input").Columns("B:B").Delete Shift:=xlToLeft
End Sub

Macro saved as CSV stops Macro from working

1 Answers1