Archive

Archive for September, 2015

I’ll see you on the dark side of the moon…

2015/09/27 Leave a comment 
 
      ... ove mirabilmente era ridutto
      ciò che si perde o per nostro diffetto,
      o per colpa di tempo o di Fortuna:
      ciò che si perde qui, là si raguna.

Will pass us by and you’ll never have to see it again, okay? – So it won’t hit us? – Not a chance.

You are receding. Before you know it you’re frozen. Until 2033, 50th.

Categories: Personal

How to split an Excel workbook into one file per sheet with VBA (reworked)

2015/09/24 1 comment
  1. There are other code snippets on stackoverflow.com, but I went with the top match in the Google search.
  2. Couldn’t get it to work (error # 424 Object required on xWS.copy in Excel 2013, with the VBA run from a separate utilities workbook) until I made these changes: 
'TRP reworked http://www.extendoffice.com/documents/excel/628-excel-split-workbook.html
Sub Workbook_Split() 'Updated by TRP 20150924

    Dim xPath As String
    Dim myWorkSheet As Worksheet
    xPath = Application.ActiveWorkbook.Path 'the source workbook needs be saved first
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each xWS In ActiveWorkbook.Sheets    'trp: replaced ThisWorkbook.Sheets
        'trp: replaced xWS.Copy 'toask: this fails with 424: object required
        Set myWorkSheet = xWS
        myWorkSheet.Copy 'If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.
        Application.ActiveWorkbook.SaveAs FileName:=xPath & "\" & myWorkSheet.Name & ".xlsx" 
        'trp: replaced xWS.Name & ".xls"
        Application.ActiveWorkbook.Close False    'trp:savechanges:=False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Categories: service-is-programming Tags: , ,