The code below presumes
1. The data is in sheet called Sheet1
2. A temporary sheet names myTempSheet can be created
3. The CSV file name to be saved in tempCSV.csv
4. No blank column is requried
To Use
After pasting the code,execute the macro. It will ask you how you want to have the column appear in the CSV. So if the first column you want is D, the correct answer is 4. Once you are done with informing the how columns would appear, just press ok without any value.
Code:
Sub createFile()
Dim numOfCol As Integer
Dim colNum As Integer
Dim myTempSheet As String
Dim dataSheet As String
Dim colValue As Variant
Dim ColIndex() As Integer
Dim cvsName As String
ThisWorkbook.Save
myTempSheet = "myTempSeet"
dataSheet = "Sheet1"
cvsName = "tempCSV"
Sheets(dataSheet).Select
numOfCol = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim ColIndex(numOfCol)
For colNum = 1 To numOfCol
colValue = InputBox("What column should be in position " & colNum & " in the CVS file", "Col Position")
If colValue = "" Then
Exit For
Else
ColIndex(colNum) = colValue
End If
Next
On Error Resume Next
Sheets(myTempSheet).Delete
On Error GoTo 0
Sheets.Add
ActiveSheet.Name = myTempSheet
For colNum = 1 To numOfCol
If ColIndex(colNum) > 0 Then
Sheets(dataSheet).Select
Columns(ColIndex(colNum)).Select
Selection.Copy
Sheets(myTempSheet).Select
Columns(colNum).Select
ActiveSheet.PasteSpecial xlValue
Else
Exit For
End If
Next
Sheets(myTempSheet).Select
numOfCol = Cells(1, Columns.Count).End(xlToLeft).Column
For colNum = 1 To numOfCol
If Cells(1, colNum) <> "" Then
Cells(1, colNum) = "(" & Cells(1, colNum) & ") "
End If
Next
ActiveWorkbook.SaveAs _
Filename:=cvsName & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=True
End Sub