Parse Functions‎ > ‎

Save Areas to WB

Specifications:
  1. A sheet has various regions of data, like invoices, separated by blank rows
  2. Use the AREAS function to save each region of data to its own workbook
  3. NOTE:  The SaveAs command for Excel 2007+ is longer than for earlier versions.  Use the correct SaveAs for your version of Excel.

CODE:

Sub NewBooks()
'Jerry Beaucaire  (9/17/2009)
'Use of Areas to save of contiguous areas as separate wbks
Dim LR As Long, i As Long, Rng As Range

LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:G" & LR).SpecialCells(xlCellTypeConstants)

    For i = 1 To Rng.Areas.Count
        Rng.Areas(i).Copy
        Workbooks.Add
        ActiveSheet.Paste
        ActiveWorkbook.SaveAs Filename:="SavedRegion" & i, xlNormal
        'ActiveWorkbook.SaveAs Filename:="SavedRegion" & i & ".xlsx", 52   'For Excel 2007+
        ActiveWorkbook.Close
    Next i
   
End Sub



Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!
Comments