+ Reply to Thread
Results 1 to 3 of 3

I would like to split one worksheet into many files based on criteria in two columns

  1. #1
    Registered User
    Join Date
    03-25-2015
    Location
    St. Louis
    MS-Off Ver
    2010
    Posts
    3

    Exclamation I would like to split one worksheet into many files based on criteria in two columns

    Here is the code I have thus far. It works fine for basing criteria one column but I need to be based on two. Have multiple consultants but the may have two different types of accounts. These types of accounts would be my sub criteria to the consultant column. I also would need the file to be saved something like SRBD Detail File , then current month and year. I would also like it to sum columns M and N.

    Option Explicit
    Sub ParseItems()
    'Jerry Beaucaire (4/22/2010)
    'Based on selected column, data is filtered to individual workbooks
    'workbooks are named for the value plus today's date
    Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
    Dim ws As Worksheet, MyArr As Variant, vTitles As String, SvPath As String
    'Sheet with data in it
    Set ws = Sheets("Sheet4")
    'Path to save files into, remember the final \
    SvPath = "H:\"
    'Range where titles are across top of data, as string, data MUST
    'have titles in this row, edit to suit your titles locale
    vTitles = "A1:V1"

    'Choose column to evaluate from, column A = 1, B = 2, etc.
    vCol = Application.InputBox("What column to split data by? " & vbLf _
    & vbLf & "(A=1, B=2, C=3, etc)", "Which column?", 1, Type:=1)
    If vCol = 0 Then Exit Sub
    'Spot bottom row of data
    LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row
    'Speed up macro execution
    Application.ScreenUpdating = False

    'Get a temporary list of unique values from key column
    ws.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True
    'Sort the temporary list
    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    'Put list into an array for looping (values cannot be the result of formulas, must be constants)
    MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE2:EE" & Rows.Count).SpecialCells(xlCellTypeConstants))
    'clear temporary worksheet list
    ws.Range("EE:EE").Clear
    'Turn on the autofilter, one column only is all that is needed
    ws.Range(vTitles).AutoFilter
    'Loop through list one value at a time
    For Itm = 1 To UBound(MyArr)
    ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)

    ws.Range("A1:A" & LR).EntireRow.Copy
    Workbooks.Add
    Range("A1").PasteSpecial xlPasteAll
    Cells.Columns.AutoFit
    MyCount = MyCount + Range("A" & Rows.Count).End(xlUp).Row - 1

    ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & Format(Date, " MM-DD-YY"), xlNormal
    'ActiveWorkbook.SaveAs SvPath & MyArr(Itm) & Format(Date, " MM-DD-YY") & ".xlsx", 51 'use for Excel 2007+
    ActiveWorkbook.Close False

    ws.Range(vTitles).AutoFilter Field:=vCol
    Next Itm
    'Cleanup
    ws.AutoFilterMode = False
    MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " & MyCount & vbLf & "Hope they match!!"
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: I would like to split one worksheet into many files based on criteria in two columns

    paulfields4 welcome,
    You forgot to use code tags.
    Can you send / attach a sample of your file
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    03-25-2015
    Location
    St. Louis
    MS-Off Ver
    2010
    Posts
    3

    Re: I would like to split one worksheet into many files based on criteria in two columns

    Sorry can't send it. Can you please work with code provided? Is there a way just to add a second criteria and just sum columns m and n?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. split text files stored in some location to multiple files based on a condition
    By GIRISH_KH in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-01-2013, 11:32 AM
  2. Split worksheet columns into more columns (same worksheet) based on numer of rows
    By Ribka40 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2013, 10:49 AM
  3. [SOLVED] Cut and Paste Rows from one worksheet to another based on criteria in two columns
    By GatorsBucs in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-08-2013, 12:19 PM
  4. [SOLVED] Split Workbook into Multiple Files based on Worksheet Name + Cell Value
    By CRW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-11-2012, 07:05 AM
  5. [SOLVED] Split worksheet into new files
    By Jason in forum Excel General
    Replies: 3
    Last Post: 04-20-2006, 01:45 PM

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