Exporting Certain Cells to a New CSV - with Custom Headers

xAcrosonicx

New Member
Joined
Sep 14, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Sep 14, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Try this:

VBA Code:
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Sep 14, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Sep 14, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top