Results 1 to 1 of 1

splitting data into multiple tabs based on column criteria

Threaded View

  1. #1
    Registered User
    Join Date
    02-21-2014
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    21

    splitting data into multiple tabs based on column criteria

    I have testing data that I commonly need to separate out into different teachers and schools. I want to separate the file into separate sheets using criteria from a given column, and then move each sheet to a new book. (I often have to do some variation of this with a variety of files... I imagine this is a common task.) I've found the following code that does most of what I want, except for a couple things as mentioned below.

    Sub parse_data()
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim icol As Long
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    vcol = 8
    Set ws = Sheets("Sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "1:6"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 To lr
    On Error Resume Next
    If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
    ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
    End If
    Next
    myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
    ws.Columns(icol).Clear
    For i = 2 To UBound(myarr)
    ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
    If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
    Else
    Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
    End If
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    End Sub
    Goal #1 - it only copies one row of my header, but my header usually takes 6 or 7 rows, all of which should be copied as is into each new sheet.
    Goal #2 - I'd like it to also move each sheet to a new book

    Any assistance on accomplishing these goals would be greatly appreciated. Samples are attached and linked below. Thanks!

    File Sample - Split into tabs.xlsx (simple workbook)
    File Sample - Split into tabs.xlsm (macro-enabled with above code)

    https://drive.google.com/file/d/0B0N...it?usp=sharing (macro-enabled with the above code)
    https://drive.google.com/file/d/0B0N...it?usp=sharing (simple workbook)
    Last edited by bardobhb; 08-27-2014 at 02:14 AM. Reason: added attachments

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] copy Tabs, based on two criteria in column J and paste values
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-13-2013, 03:46 PM
  2. Replies: 2
    Last Post: 07-09-2012, 01:44 PM
  3. Splitting data into multiple tabs
    By jadams0550 in forum Excel General
    Replies: 6
    Last Post: 01-06-2012, 06:47 AM
  4. Splitting out Excel data to multiple files based on one column's values
    By machos in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2011, 08:55 AM
  5. MACROS: Sort by multiple criteria - Create tabs based off of criteria
    By svineyard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2009, 10:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1