Saving csv file - help needed putting code together

[enthusiastic amateur]

Hi All,
I'm hoping you can help as I've got so far and hit a problem (as I'm new to this)

I have some code that saves a range that it works out is active based on the number of rows and saves it to a specific location, however when testing I found that it wasn't putting " marks where needed and not all columns need " marks.

Looking at all the notes in different forums, getting " to work correctly is painful, so to get around this I concatenate the relevant columns adding comma's and quotes as needed into a single column that I now want to save.

I tried changing the original code and it puts in a double "" where I only want a "... it looks ok when you open the new csv file in excel, but when you open the file in notepad.. they they are.

I've found some code that saves it nicely as a csv file, putting the correct number of " in it, but does not pick up the only elements I need from the original code i.e. Identify the range tthat is needed as rows that contain values within column Z OR to save the file with the right name format in the same folder as the active sheet.

Not sure if it's better to adapt the new code or the original and I've been trying to stitch them together unsuccessfully- can anyone help?

I also need to work out if I can concatenate in VBA rather than in columns in excel as it's a more robust solution
Thanks


ORIGINAL CODE
'SAVING - Saves the file as csv (no validation check)
Sub ORIGINAL_Save_CSV_6_Tabs_CalcRange()

'Turn off screen updating for performance and prevent dizziness
Application.ScreenUpdating = False

Dim MyPath As String
Dim MyFileName As String
Dim WB1 As Workbook, WB2 As Workbook

Set WB1 = ActiveWorkbook
MyFileName = "ProdTabData-" & Format(Date, "yyyymmdd-") & Format(Time, "hhmmss")
FullPath = WB1.Path & "\" & MyFileName

'System detected output range, based on
Application.ScreenUpdating = False

Range("Z5:Z" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
Set WB2 = Application.Workbooks.Add(1)
WB2.Sheets(1).Range("Z5").PasteSpecial xlPasteValues
Application.DisplayAlerts = False

'save file
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
With WB2
.SaveAs Filename:=FullPath, FileFormat:=xlCSV, CreateBackup:=False
.Close False
End With
Application.DisplayAlerts = False

'Turn on screen updating
Application.ScreenUpdating = True

End Sub



NEW SAVE CODE - that I want to add the dynamic range identification and save location into
'http://www.excel-easy.com/vba/examples/write-data-to-text-file.html
Sub test4()
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
myFile = Application.DefaultFilePath & "\sales.csv"

Set rng = Selection

Open myFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, 1).Value
If j = rng.Columns.Count Then
Write #1, cellValue
Else
Write #1, cellValue,
End If
Next j
Next i
Close #1

End Sub



3rd option - open notepad and save directly into there, but need to save location and dynamic selection adding


I'm hoping the above are the ingredients... I just don't know what order to put them in

Thanks
AE

NOT SOLVED, BUT APPROACHED IT A DIFFERENT WAY - created new question about to be raised separately