1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: how can you create multiple separate sheets from one sheet xls?

Discussion in 'Business Applications' started by tjamnz, Oct 3, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. tjamnz

    tjamnz Thread Starter

    Joined:
    Jun 15, 2004
    Messages:
    775
    Hello..

    type
    xyz
    xyz
    xyz
    ccc
    ccc
    ccc
    ccc
    ccc
    ccc
    zzz
    zzz
    zzz
    zzz

    etc... but several thousand rows each with their own "type" ordered by type

    how can i create a separate spreadsheet for each type...

    dont know what this process is called...

    surely there is a faster way than copying each type and pasting into a new xls doc...

    (dont want to create tabs)

    Thanks for any advice
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  3. tjamnz

    tjamnz Thread Starter

    Joined:
    Jun 15, 2004
    Messages:
    775
    didnt work..

    i tried a test run.. and went forward with debugging...

    there's some code in there that wouldnt work for my sheet...

    anyways.. on a brighter note.. its sparked my interest in vba for excel...

    i think i'll take the course with my account on vtc.com


    (y)
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Do you want to create a brand new workbook for each type in the list or a new worksheet within the same workbook? Either way it is very easy with a few lines of code. Let me know if you want assistance still.

    Regards,
    Rollin
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I read that as you want one new workbook per type. While it's unlikely that there are hundreds of types (if there were, the data wouldn't fit on a sheet at several thousand rows per type), a macro may struggle to do this for "many" types on account of available memory.

    See the code in the attached.

    "Section 1" creates test data on Sheet1 (12000 rows, 6 types).

    "Section 2" creates a types table on a new added sheet ("Filter").

    "Section 3" (a) creates a new workbook for each type in the types table (b) copies the test data rows to the new workbook. Important: the test data is ordered by type ; your data must be ordered by type (as you say it is) for this to work correctly.

    "Section 4" is just maintenance (dumps the "Filter" sheet).

    HTH

    Sub test()

    Application.ScreenUpdating = False

    'Section 1
    Sheets("Sheet1").Select
    Range("A2:A2000") = "aaa"
    Range("A2001:A4000") = "bbb"
    Range("A4001:A6000") = "ccc"
    Range("A6001:A8000") = "ddd"
    Range("A8001:A10000") = "eee"
    Range("A10001:A12000") = "fff"

    'Section 2
    OriginalWb = ActiveWorkbook.Name
    OriginalSh = ActiveSheet.Name
    Sheets.Add
    ActiveSheet.Name = "Filter"
    Sheets("Sheet1").Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range("A1"), Unique:=True
    Rows(1).Delete

    'Section 3
    For Each c In Range("A1").CurrentRegion
    cRow = Application.Match(c, Sheets(OriginalSh).Range("A:A"), 0)
    cCount = WorksheetFunction.CountIf(Sheets(OriginalSh).Range("A:A"), c)
    Workbooks.Add
    Workbooks(OriginalWb).Sheets(OriginalSh).Cells(cRow, 1).Resize(cCount).EntireRow.Copy Range("A1")
    Workbooks(OriginalWb).Activate
    Next c

    'Section 4
    Application.DisplayAlerts = False
    Workbooks(OriginalWb).Sheets("Filter").Delete
    Application.DisplayAlerts = True

    Application.ScreenUpdating = False

    End Sub
     

    Attached Files:

  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/632944

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice