Split worksheets into separate excels AND only save those cells with data in them.

[james00]

Hi all.

I got help with setting up the VBA script to split the workbook into separate sheets via another post of mine – “VBA script - split sheets into unique folder named of cell entry”:

VBA script - split sheets into unique folder named of cell entry

It works great, but I am now trying to figure out is how to add some VBA code to save the split excel sheets into DATA ONLY sheets.

Right now it saves each sheet with all my functions and formulas, so many cells, although “blank” if criteria are not met, still have the functions/formulas in them, like IF(ISBLANK) etc...

I'd like these cells that should not return data if criteria are not met to be saved BLANK, ie nothing in those cells*

Any ideas would be greatly appreciated.

 

[mumps]

Check your original thread for a suggested solution.

 

[james00]

@mumps

Your solution works great, but still one thing missing. Perhaps I did not state it clearly, my bad.

Only cells that do meet the criteria are exported to the new sheets, BUT, these cells in the new sheets STILL have the formulas.

How can the data only be exported, ie the value returned when the criteria is met?

Right now if I was to make changes in the main workbook these would be updated in the new SHEET files we created.

 

[Fuji]

Try

Sub test()
    Dim ws As Worksheet, i&, myDir$, f$, wsName$
    Application.ScreenUpdating = False
    myDir = ThisWorkbook.Path
    f = ThisWorkbook.Sheets(1).[b2]
    If Dir(myDir & "\" & f, 16) = "" Then MkDir myDir & "\" & f
    myDir = myDir & "\" & f
    Set ws = Workbooks.Add(xlWBATWorksheet).Sheets(1)
    For i = 2 To ThisWorkbook.Sheets.Count
        wsName = ThisWorkbook.Sheets(i).Name
        ws.UsedRange.Clear
        ThisWorkbook.Sheets(i).Cells.Copy
        ws.[a1].PasteSpecial xlPasteAll
        ws.[a1].PasteSpecial xlPasteValues
        ws.Name = wsName
        ws.Parent.SaveAs myDir & "\" & wsName, 51
    Next
    Application.CutCopyMode = False
    ws.Parent.Close False
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Please continue in your original thread.