Converting XLS worksheets into separate (CSV) files

Today I had a large Excel file (workbook) with many tabs (worksheets), and I needed each tab in a separate file as they needed to be shared with different people (who should not be able to see the other tabs).

Yes, you could make X copies of the XLS file and then manually delete all sheets but one in each file, but hey, that is for people who enjoy pain. The much easier way is to do it with a simple macro. To split an XLS file into separate XLS files for each tab, simply open the XLS file you want to split. Then open the VBA editor (press ALT + F11) and copy the following VBA code into the a new module (go under "Insert" menu, and select "Module"). Close VBA Editor (no need to save), and back in XLS file, go under "View", select "Macros" and "View macros". Now simply run the new macro called ConvertTabsToFiles. No need to save the macro. Just run it.

Sub ConvertTabsToFiles()
  Dim currPath As String
  currPath = Application.ActiveWorkbook.Path
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=currPath & "\" & xWs.Name & ".xlsx", CreateBackup:=False
    Application.ActiveWorkbook.Close False
  Next
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub


You should now have a new XLS file for each worksheet in the original XLS file.  If your XLS file is of extension .xls and not .xlsx, simply change the xlsx extension to xls in the code above @ line 8. Otherwise your new files will be of the wrong format (but still readable)

If you want to save several sheets to CSV, simply use this line instead of the one saving to xlsx
Application.ActiveWorkbook.SaveAs Filename:=currPath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False

And if you run into issues that your file "cannot be saved in macro-free workbooks", simply save to the file format called xlsm (XLS macro-enabled workbook).

Nifty,
Thomas

Random date generation

Today I needed to generate a large (bulk) set of random dates for a project at work.  Probably a million ways to do this, but I turned to Excel. I've used the RAND function before and was expecting to use something ugly like this for month:
=INT(RAND()*12)+1

But as I'm always looking for new cool ways to be more efficient, I stumbled upon this cool function in XLS. Not sure when it was introduced, but it is cool. Simply use RANDBETWEEN in combination with the DATE function.  To generate a random date between 2 dates, simply use:
=RANDBETWEEN(DATE(2014,1,1),DATE(2014,7,15))

Note that new random data is calculated every time calculations are performed, like when you add a new formula. RAND works the same way.

Nifty,

Thomas