Saving File as CSV type

[kiwi6682]

Hi there,

I am struggling just getting one piece of my code right. I'm trying to copy a certain range of data from one worksheet into a separate file and save it as a csv file. See below - its everything I want it to do except it's saving as an Excel workbook rather than csv. Is there a simple fix? One thing to point out is that I want the save as window to remain open...that's why I'm not actually closing the window and saving it. I tried changing the very last part from xlsx to csv1 but that didnt work...any ideas?

Thanks in advance!!

Dim LastRow As Long
ActiveSheet.Select
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("A1").Select
Range(Cells(8, 1), Cells(LastRow, 19)).copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.CutCopyMode = False
Dim ThisFile As String
ThisFile = "Upload_for_MMM"
Application.Dialogs(xlDialogSaveAs).Show ThisFile & ".xlsx"

 

[BengalMagic]

Hi there,

I am struggling just getting one piece of my code right. I'm trying to copy a certain range of data from one worksheet into a separate file and save it as a csv file. See below - its everything I want it to do except it's saving as an Excel workbook rather than csv. Is there a simple fix? One thing to point out is that I want the save as window to remain open...that's why I'm not actually closing the window and saving it. I tried changing the very last part from xlsx to csv1 but that didnt work...any ideas?

Thanks in advance!!

Dim LastRow As Long
ActiveSheet.Select
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("A1").Select
Range(Cells(8, 1), Cells(LastRow, 19)).copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.CutCopyMode = False
Dim ThisFile As String
ThisFile = "Upload_for_MMM"
Application.Dialogs(xlDialogSaveAs).Show ThisFile & ".xlsx"

I see nothing in your code that tells Excel that you want to save as csv?

 

[kiwi6682]

Exactly....thats what I"m trying to figure out?? Like I said - I had the very last part as csv1 rather than xlsx but that didnt work.

 

[BengalMagic]

Exactly....thats what I"m trying to figure out?? Like I said - I had the very last part as csv1 rather than xlsx but that didnt work.

ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, FileFormat:=xlCSV

 

[kiwi6682]

I tried this as well but that closes the save as window. And I need to modify the file name each time upon using this macro which is why I need that window open.