VBA to create CSV but not include blank rows

[SophieB]

Hi all

I've managed to cobble together some code (I'm new to this!) to create a CSV from a formulated tab in a spreadsheet. This new csv should be called the name of the tab plus today's date. It will then prompt the user as to where to save this csv.

It all works fine apart from 3 small things, which I'm sure are very simple if you know what you're doing...

1) My dates are coming out as mm/dd/yyyy rather than dd/mm/yyyy
2) I don't want to include columns V,W or X
3) In my formulated tab, I've used a lot of IFs which have returned "" but can't seem to work out how to not include these entirely blank rows in the output csv

Code below:

Function FormatShortdate()




Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(3)
sh.Range("M:M").NumberFormat = "dd/mm/yyyy"
End Function


Sub Export()
Dim MyPath As String
Dim MyFileName As String


MyFileName = "Abbas_Sage_Import_" & format(Date, "ddmmyyyy")


Sheets("Abbas-Sage Import").Copy
Set rng = Sheets("Abbas-Sage Import")


With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = "" '<~~ The start folder path for the file picker.
If .Show <> -1 Then GoTo NextCode
MyPath = .SelectedItems(1) & ""
End With


NextCode:


With ActiveWorkbook
.SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close False
End With
End Sub


I'd HUGELY appreciate some help with this!

Thank you

Sophie

 

[Yongle]

variable MyfilenNme is not declared and has not been assigned a value

looking at original VBA perhaps this needs including in your code ...

Dim MyFileName As String
MyFileName = "Abbas_Sage_Import_" & format(Date, "ddmmyyyy")

 

[SophieB]

That's perfect, thank you so SO much!

The only issue I have now though, which really is tiny, is that the whole spreadsheet closes down after the code is ran. Not a huge issue obviously as we can reopen, just a minor inconvenience lol!

Do you know how to stop this from happening, or would I need to start a new thread? Sorry for all of the questions, I thought I might as well make it work as perfectly as I can get it!

Thank you

 

[Yongle]

This is untested (currently travelling), but, creating a new workbook and pasting the values to that applying the same code should leave the original workbook open. Test on a copy of your workbook

1. add this to declare the variable required

    Dim NewBook As Workbook

2. replace

    Set WS = Sheets.Add(After:=ActiveSheet)

with

    Set NewBook = Workbooks.Add
    Set WS = NewBook.Sheets(1)

3. (where the csv is being created) replace

    With WS

with

    With NewBook

4. remove the code which deleted the temporary sheet

Any problems, post your complete macro and I will test it when back at my PC

 

[SophieB]

Thank you very much, that's worked absolutely perfectly and I now have one very happy team! THANK YOU!!

 

[Yongle]

Glad it worked. Thanks for the feedback