Exporting Certain Cells to a New CSV - with Custom Headers

[xAcrosonicx]

Hello,

Does anyone know how I could create a button in my workbook to export certain cells into a new CSV file? For example, here is some data:

Cell A1: John
Cell A2: Doe
Cell A3: Irrelevant
Cell A4: Irrelevant
Cell A5: Manager Name
Cell A6: Job Title

I would like to be able to pull cells A1, A2, A5, and A6 into a new CSV file. A1 in the new CSV would have the header label "Firstname", B1 would be "Lastname", C1 would be "Manager", D1 would be "Job TItle" and then it would pull the information from the original file's A1 cell and paste that into the new A2 cell, old A2 would become B2, old A5 would become C2, and old A6 would become D2. Hopefully this makes sense, and I would like to create a Macro where I can just push a button and have it automatically create this file that generates the CSV name data from A1,A2, and "Okta.csv" - so the example would be JohnDoe.csv.

Thanks!

 

[xAcrosonicx]

Try this:

Sub Macro()
  Dim wb2 As Workbook, n As Long
  Dim sName As String, suffix As String
 
  Application.ScreenUpdating = False
  With ActiveSheet
    Set wb2 = Workbooks.Add
    Range("A1").Resize(1, 4).Value = Array("Firstname", "Lastname", "Manager", "Job TItle")
    Range("A2").Resize(1, 4).Value = Array(.[A1], .[A2], .[A5], .[A6])
   
    Do
      If sName <> "" And suffix = "" Then
        suffix = "Okta"
      ElseIf Left(suffix, 4) = "Okta" Then
        n = n + 1
        suffix = "Okta" & n
      End If
      sName = ThisWorkbook.Path & "\" & .[A1] & .[A2] & suffix & ".csv"
    Loop While Dir(sName) <> ""
   
    wb2.SaveAs sName, xlCSV
    wb2.Close False
  End With
 
  Application.ScreenUpdating = True
End Sub

It also still replaces the text in the original file, which I do not want.

 

[DanteAmor]

This new version does not replace the file.
First check if the JohnDoe file already exists.
If it already exists, create the JohnDoeOkta file,
if JohnDoeOkta already exists, create JohnDoeOkta1,
if JohnDoeOkta1 already exists, then create JohnDoeOkta2,
and then JohnDoeOkta3 and so on.
Check the folder, every time you run the macro, it generates a new file.

 

[xAcrosonicx]

This new version does not replace the file.
First check if the JohnDoe file already exists.
If it already exists, create the JohnDoeOkta file,
if JohnDoeOkta already exists, create JohnDoeOkta1,
if JohnDoeOkta1 already exists, then create JohnDoeOkta2,
and then JohnDoeOkta3 and so on.
Check the folder, every time you run the macro, it generates a new file.

After closing and re-opening Excel, it no longer overwrites the original file. However, it still gives me the "bad file name or number"error every time and creates a new document named "Book1" which contains the proper data but does not save anywhere and is defaulted to xlsx when it asks me if I want to save it (likely because of the error message I mentioned).

 

[DanteAmor]

What do you have in cells A1 and A2 when the error happens?
You could upload a copy of your book that you are having trouble with.
In my tests everything works fine. So you have a problem with your book, I must review it.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[xAcrosonicx]

What do you have in cells A1 and A2 when the error happens?
You could upload a copy of your book that you are having trouble with.
In my tests everything works fine. So you have a problem with your book, I must review it.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

I was able to determine that it was an issue because my file was located in OneDrive. For whatever reason, it worked when I removed it from OneDrive. Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.