VBA – Split worksheet to worksheets, save Excel worksheets to csv

This article does 2 things:

  • Splits one worksheet to multiple worksheets
  • Then goes through the worksheets and saves them as *.CSV files

I hope that is enough for you.

#RandomMountain

Ok, so we start with the following Excel file like this:

Make sure to declare the worksheet with the inital data here correspondingly – Public Const WKS_TO_KEEP As String = "Tabelle1". In my case, it is "Tabelle1".

 

And the idea is to split this main worksheet into multiple other worksheets, keeping the title [Alpha, Bravo, Charlie, Delta] and starting each fifth row on a new worksheet. Like this:

The first worksheet with name 1 contains the values from the first 5 rows. This is the second, it is named 6 and it contains the next 5 values, from row 6 to row 10.

The fifth row is optional, of course. The interesting part is that we got all the data split into new worksheets. Then, the last step is to create a separate CSV file from each of those worksheets:

“CSV_FILE” comes from Public Const CSV_NAME As String = "CSV_FILE"

This is pretty much all. The code to get this done is below:

What is worth mentioning is the following:

  • To run the first step, run SplitMe() , for the second step run MakeMeACSV().
    • The CSV files are made in the folder of the Excel file, in which the code resides.
  • The constants on the top of the code are actually rather important:
    • Make sure that you have a worksheet named “Tabelle1” or it will not work.
    • Furthermore “Tabelle1” is the worksheet, that should contain the initial data, to be splitted.
  • If you make a mistake, use DeleteAllButOne(), which will delete all worksheets, but the one declared with this one – WKS_TO_KEEP.
  • In my code, I am deleting the first column in the CSV file (Alpha), because I do not need it.
    • Just comment out this line –  myNewWorkbook.Worksheets(1).Columns(1).Delete , if you want to keep it.

That’s all! I have even added this one to my boilerplate!

Tagged with: , , ,