Excel Macros

Split Excel Data Into Multiple Worksheets based on First Cell's Text



http://www.excelforum.com/excel-programming/713770-split-worksheet-content-into-multiple-worksheets.html


Option Explicit

Sub ParseSiteData()
'JBeaucaire  (11/11/2009)
'Based on column A, data is filtered to individual sheets
'Creates sheets and sorts alphabetically in workbook
Dim LR As Long, i As Long, MyArr
Dim MyCount As Long, ws As Worksheet
Application.ScreenUpdating = False

Set ws = Sheets("Data")      'edit to sheet with master data
ws.Activate

Rows(1).Insert xlShiftDown
Range("A1") = "Key"
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("CC1"), Unique:=True
Columns("CC:CC").Sort Key1:=Range("CC2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
MyArr = Application.WorksheetFunction.Transpose(Range("CC2:CC" & Rows.Count).SpecialCells(xlCellTypeConstants))

Range("CC:CC").Clear
Range("A1").AutoFilter

For i = 1 To UBound(MyArr)
    ws.Range("A1").AutoFilter Field:=1, Criteria1:=MyArr(i)
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    If LR > 1 Then
        If Not Evaluate("=ISREF('" & MyArr(i) & "'!A1)") Then
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MyArr(i)
        Else
            Sheets(MyArr(i)).Move After:=Sheets(Sheets.Count)
            Sheets(MyArr(i)).Cells.Clear
        End If
        ws.Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
            Sheets(MyArr(i)).Range("A1")
        ws.Range("A1").AutoFilter Field:=1
        MyCount = MyCount + Sheets(MyArr(i)).Range("A" & Rows.Count).End(xlUp).Row - 1
        Sheets(MyArr(i)).Columns.AutoFit
    End If
Next i

ws.Activate
ws.AutoFilterMode = False
LR = ws.Range("A" & Rows.Count).End(xlUp).Row - 1
Rows(1).Delete xlShiftUp
MsgBox "Rows with data: " & LR & vbLf & "Rows copied to other sheets: " & MyCount & vbLf & "Hope they match!!"
Application.ScreenUpdating = True
End Sub

Comments

Post a Comment

Popular posts from this blog

Excel - Adding an existing Pivot table to the data model

Image
  I've been working more with, and continue to be amazed by, Power Pivot. I recently ran into an issue where I needed to add the data for an existing Power Pivot report to the Data Model. There is a checkbox that makes this easy when creating a PivotTable: As so often happens, the analysis grew in scope/importance and I found myself needing to add the data to the data model. While it's not obvious how, there is a way to add an existing Pivot table to the data model. There's a More Tables... link in the PivotTable Fields pane.  Clicking on the  More Tables... results in a dialog confirming that you want to Create a New PivotTable .  Doing so will result in the creation of the new PivotTable in a new sheet. By default the data is added to the data model. You'll likely lose some formatting but for anything other than a trivial PivotTable, this should save effort, as it did for me.
Read more

Mirth

Image
Introduction Let's talk about Mirth. At it's simplest, Mirth  link is an Open Source HL7 interface engine. HL7 (Health Level 7) is an NPO involved in the development of healthcare standards and the HL7 v2.x and HL7 v3 standards are commonly used in Medical Information for data interchange - including but not limited to clinical, financial, logistical and administrative messaging. I first ran across Mirth over a year ago on a personal quest to find an alternative to our client's legacy HL7 application. Academically, I wrote a number of lexical analysis tools using lexx, yacc, bison and JavaCC and knew the challenges of writing and maintaining complex parsers. I was simply looking for something that could parse HL7 for us, and perhaps log messages and provide a little more transparency than our application currently allowed.  A year later and our legacy application is still going strong (read: my proposal was declined), but Mirth is being used in production, far away from th
Read more

Getting Started with Mirth (Part 1)

Image
This is the first post in a 2 part series. The second post is here Recently I posted about my positive experience when using Mirth by Webreach as an open source communication layer between one of our in-house applications and a third party and proprietary web service interface. That post received a large amount of attention (relative to my other posts :o) ) so now I will post a quickstart guide to creating a similar setup. In this post I will go over the very basics of using Mirth (please dig in and play around with some more advanced options/configurations), specifically I will demonstrate how to use Mirth to read data from a database, package the data up in a SOAP envelope, send the data to a web service and process the SOAP response. I'll also show you how easy it is to monitor channels once deployed. Once you've installed and opened Mirth for the first time you'll be presented with an empty dashboard - but it wont stay empty for long. This is where you'll s
Read more