Page 3 of 12 FirstFirst 12345 ... LastLast
Results 21 to 30 of 111

Thread: VBA: Split data into multiple worksheets based on column

  1. #21
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by RoryA View Post
    Like I said, you need to declare i as Long, not Integer. …
    Especially as i is the “Bound Variable” and all other Loop Variables will be forced ( “coerced”?) in the loop workings to be of its Type regardless of how they may be dimensioned and used elsewhere in the code ………..






    ……
    .. joeyc123,
    The code looks a bit similar to ones I have done in threads.. If you are still having problems, maybe you can post some sample table showing the sort of data you have and importantly wot results you expect to get based on that data…

    … If you are not sure how to do that some notes on ways to do it:-

    . 1 If you can, try uploading this, https://onedrive.live.com/?cid=8cffd...CE27E813%21189 instructions here MrExcel HTML Maker . This free Excel add-In is good for screen shots here of spreadsheets. Then everyone can quickly see what is going on and follow the Thread easily.
    Or
    . 2 Up left in the Thread editor is a table icon. Click that, create an appropriately sized table and fill it in. (To get this icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)
    Or
    . 3 Supply us with example Excel files (Can of course be shortened, or made - up data in case any info is sensitive)
    . For example send over this free thing: Box Net,
    Remember to select Share after uploading and give us the link they provide.

  2. #22
    New Member
    Join Date
    Nov 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    [QUOTE=DocAElstein;3987607]Especially as i is the “Bound Variable” and all other Loop Variables will be forced ( “coerced”?) in the loop workings to be of its Type regardless of how they may be dimensioned and used elsewhere in the code ………..

    Hi DocAElstein,

    This is the sample data i am working on. Basically i want Column C (called, "Record") as the reference so each unique record will be on separate sheet where the name of the sheet tab follows the Record name.

    Date Currency Record Mid %Change
    08/01/2014 7:31:18 CHF CHF_2Y_3Y 4.25 6.25%
    08/01/2014 13:33:22 CHF CHF_2Y_3Y 4 -5.88%
    08/01/2014 13:48:48 CHF CHF_2Y_5Y 4.25 6.25%
    08/01/2014 15:55:48 CHF CHF_2Y_5Y 4 -5.88%
    08/04/2014 7:30:01 CHF CHF_2Y_3Y 4 0.00%
    08/04/2014 8:18:15 CHF CHF_2Y_3Y 3.75 -6.25%
    08/04/2014 8:19:24 CHF CHF_2Y_3Y 4 6.67%
    08/04/2014 8:25:45 CHF CHF_2Y_3Y 4 0.00%
    08/04/2014 8:40:37 CHF CHF_2Y_3Y 3.75 -6.25%

  3. #23
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by joeyc123 View Post
    .......
    .....

    This is the sample data i am working on. Basically i want Column C (called, "Record") as the reference so each unique record will be on separate sheet where the name of the sheet tab follows the Record name.....
    . ..........
    .....................

    . OK.
    . That looks simple enough even for me. I expect your problems are more subtle coming from your actual data as the sample data is very simple and small. But anyways…..
    …. If I assume your initial file looks like this with just one sheet in it (or only one with all data in it)…


    ABCDEF
    1DateCurrencyRecordMid%Change
    208.01.2014 07:31CHFCHF_2Y_3Y4.256.25%
    308.01.2014 13:33CHFCHF_2Y_3Y4-5.88%
    408.01.2014 13:48CHFCHF_2Y_5Y4.256.25%
    508.01.2014 15:55CHFCHF_2Y_5Y4-5.88%
    608.04.2014 07:30CHFCHF_2Y_3Y40.00%
    708.04.2014 08:18CHFCHF_2Y_3Y3.75-6.25%
    808.04.2014 08:19CHFCHF_2Y_3Y46.67%
    908.04.2014 08:25CHFCHF_2Y_3Y40.00%
    1008.04.2014 08:40CHFCHF_2Y_3Y3.75-6.25%
    11

    FullDataSheet





    . If you then apply the code I give at the end of this post then you get 2 additional sheets added which look like these

    ABCDEF
    1DateCurrencyRecordMid%Change
    208.01.2014 13:48CHFCHF_2Y_5Y4.256.25%
    308.01.2014 15:55CHFCHF_2Y_5Y4-5.88%
    4

    CHF_2Y_5Y





    ABCDEF
    1DateCurrencyRecordMid%Change
    208.01.2014 07:31CHFCHF_2Y_3Y4.256.25%
    308.01.2014 13:33CHFCHF_2Y_3Y4-5.88%
    408.04.2014 07:30CHFCHF_2Y_3Y40.00%
    508.04.2014 08:18CHFCHF_2Y_3Y3.75-6.25%
    608.04.2014 08:19CHFCHF_2Y_3Y46.67%
    708.04.2014 08:25CHFCHF_2Y_3Y40.00%
    808.04.2014 08:40CHFCHF_2Y_3Y3.75-6.25%
    9

    CHF_2Y_3Y





    . So the code is below. As I am learning I have a lot of explaining comments which look a bit confusing here. But if you copy the code in your Code window it is a bit more readable as my comments are on one line and so in the Code window (the Visual Basic Editor or development Window obtained by Alt F8) most comments “truncated off” on your screen to make it more readable!
    . The code as written is designed to be used to update files. So every time you run it the record files are deleted first if they are already there.
    . If you still have any problems then get back, and maybe send a file with more representative data in it and I can get the code working on that (assuming wot my code is doing is the sort of thing you want) and send the file back via the Thread with a link.
    . I can send the working example file with a link If you want?

    Alan

    P.s. there are lots of threads doing this sort of thing but the title is not as good as this one so does not come up so good in a search. Here are a few
    http://www.mrexcel.com/forum/excel-q...e-columna.html
    http://www.mrexcel.com/forum/excel-q...fic-value.html
    http://www.mrexcel.com/forum/excel-q...ontents-2.html

    The last one is where I “stole” the important bits for this code. So credit goes to Alan_P really for the code.

    Here it is:

    Code:
    Option Explicit 'Not necerssary but good practice to keep computer memery usage to minimum (and helps show up errors)
    Sub joeyc123AdvFiltZuNeuTab****()
    Application.ScreenUpdating = False 'Not necerssary but speeds things up a bit, by turning screen updating off.
    On Error GoTo TheEnd 'If anything goes wrong go to the End instead of crashing.
     
    Dim ws As Worksheet 'ws now has Methods and Properties of Worksheets obtained with typing . dot after it!
    'Start Bit to  Delete Sheets / Tabs------------
    Application.DisplayAlerts = False 'Prevents being asked everytime if you really want to delete the Workbook
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "FullDataSheet" Then
        ws.Delete
        Else 'Presumably then the worksheet name is FullDataSheet so
        ' do nothing (Don't delete it!)
        End If
    Next
    Application.DisplayAlerts = True 'Turn it back on
    'End Bit to delete new Sheets / Tabs------------
     
    'Add new Worksheets---
    Dim Record As String 'Record name, not kept constant, used / updated in looping
    Dim LastRecordRow As Long 'Assume initially you have no more than 255 Records. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647)
    Let Worksheets.Add(After:=Worksheets(1)).Name = "Unique1" 'Add a Worksheet after the first, named Unique1 for now
    Let LastRecordRow = Sheets("FullDataSheet").Range("C" & Rows.Count).End(xlUp).Row
    Sheets("FullDataSheet").Range("C1:C" & LastRecordRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Unique1").Range("A1"), Unique:=True 'Copies entire L Column to first column in sheet2 (Tempory made "Unique1" sheet), The important bit is Unique:=True - that only copies unique bits
    '---------------------
     
    Dim LastUnqRow As Long, UqeRow As Long 'Rows in Tempory Unique sheet. long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647)
    Let LastUnqRow = Worksheets("Unique1").Cells.Find(What:="*", After:=Worksheets("Unique1").Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Get last Unique Row for use in next loop. method: You starta at first cell then go backwards (which effectively starts at end of sheet. This allows for different excel versions with different available Row numbers)
      For UqeRow = 2 To LastUnqRow Step 1 '
        'Make new sheet------------
        If Sheets("Unique1").Cells(UqeRow, 1).Text <> "" Then 'Assuming a Record is there
        Let Record = Sheets("Unique1").Cells(UqeRow, 1).Text 'Put name in Record variable
        Let Worksheets.Add(After:=Worksheets(1)).Name = Record 'Add new worksheet with Record name
       
       
          With Sheets("FullDataSheet") 'Copying data to new sheet----
            .UsedRange.AutoFilter Field:=3, Criteria1:=Record 'Filter out everything except with that with the appropriate Record (makes visible based on the criteria only the stuff you want??)....
            .UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(Record).Range("A1") ', then combine it with SpecialCells to just copy that wot you see, (and then send it to the relavent new sheet , name n).. ( Idid notice that it works the same without the .SpecialCells(xlCellTypeVisible) bit, - but that mayjust be Excel “guessing wot you want” as it does, that is to say it copies by default wot is visible?- not too sure on that one yet.)
          End With '-------------------------------------------------
       
          With Sheets(Record).UsedRange 'Bit of simple Format Tidying up
            .WrapText = False
            .Columns.AutoFit
          End With
        Else
        'Do nothing if no Record given
        End If
        '-----------------------------
      Next UqeRow 'Go back and make another ner sheet
     
    Sheets("FullDataSheet").AutoFilterMode = False
     
    Application.DisplayAlerts = False 'Prevent being asked if you really want to delete Temporary Unique sheet
    Sheets("Unique1").Delete ' delete the filtered Record name sheet as you do not need it any more
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True 'Turn screen "back on" or screen is "dead"
    Exit Sub 'We stop code here assuming it worked (or at least did not crash!)
    TheEnd:
    Application.ScreenUpdating = True 'Important to do this here so if anything goes wron then the screen updating is turned back on, ohterwisee the screen is dead
    MsgBox (Err.Description) 'Print out error message in Message Box
    End Sub 'joeyc123AdvFiltZuNeuTab****()

  4. #24
    New Member
    Join Date
    Nov 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Hi Doc,

    The end result you showed is exactly what i am aiming for. Just remember that i am always want to filter values based on the column Record and my data may range up to 350,000 rows. I copied the code but it is not doing it. I have this below highlighted in red and the macro is not being saved.

    Option Explicit 'Not necerssary but good practice to keep computer memery usage to minimum (and helps show up errors)
    Sub joeyc123AdvFiltZuNeuTab****()
    Application.ScreenUpdating = False 'Not necerssary but speeds things up a bit, by turning screen updating off.
    On Error GoTo TheEnd 'If anything goes wrong go to the End instead of crashing.
    Last edited by RoryA; Nov 10th, 2014 at 03:13 AM.

  5. #25
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by joeyc123 View Post
    Hi Doc,

    The end result you showed is exactly what i am aiming for. Just remember that i am always want to filter values based on the column Record and my data may range up to 350,000 rows. I copied the code but it is not doing it. I have this below highlighted in red and the macro is not being saved.
    ........
    Sub joeyc123AdvFiltZuNeuTab****()..........

    . Hi
    . Simply delete those **** - I Put in a naughty word S__t and the MrExcel editor censored it!, and changed it to **** which VBA code does not like!!!
    Alan

  6. #26
    New Member
    Join Date
    Nov 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by DocAElstein View Post
    . Hi
    . Simply delete those **** - I Put in a naughty word S__t and the MrExcel editor censored it!, and changed it to **** which VBA code does not like!!!
    Alan

    oh ok lol..

    Finally, now it worked.. perfectly! Thanks so much

  7. #27
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by joeyc123 View Post
    ……….Finally, now it worked.. Perfectly! Thanks so much [IMG]file:///C:/Users/Elston/AppData/Local/Temp/msohtmlclip1/01/clip_image001.gif[/IMG]
    . You’re Welcome
    . Glad it worked for You (And somewot surprised with column data ranges up to 350,000 rows! – I have problems with my codes bombing out at around 3000 sometimes!)
    . Many thanks for the feedback
    Alan

  8. #28
    New Member
    Join Date
    Nov 2014
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by DocAElstein View Post
    . You’re Welcome
    . Glad it worked for You (And somewot surprised with column data ranges up to 350,000 rows! – I have problems with my codes bombing out at around 3000 sometimes!)
    . Many thanks for the feedback
    Alan

    Hi Doc,

    I came across another problem this time i am creating a Leave Tracker for my Team. The code below allows everyone to update the excel sheet and sends an email to our team email automatically in outlook however i want the code to also say in the email (MAYBE MSG BODY) which cell was updated and what's the content of that cell e.g. "Joey VL" for joey Vacation Leave or Joey SL for sick leave so everyone in the team know who is on leave on that particular day. Can you help me with this please?
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)
     
    Dim answer As String
     
    answer = MsgBox("Do you want to send an update to the team?", vbYesNo, "Save the file")
     
    If answer = vbNo Then Cancel = True
    If answer = vbYes Then
    'open outlook type stuff
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OlObjects = OutlookApp.GetNamespace("MAPI")
    Set newmsg = OutlookApp.CreateItem(olMailItem)
    'add recipients
    'newmsg.Recipients.Add ("ETI TEAM")
    newmsg.Recipients.Add ("eti.team@eti.com")
    'add subject
    newmsg.Subject = "2015 Leave Tracker"
    'add body
    newmsg.Body = "Leave tracker was updated"
    newmsg.Display 'display
    newmsg.Send 'send message
    'give conformation of sent message
    MsgBox "Email will be sent to Markets ETI Team", "Email Sent"
     
     
     
    End If
     
     
    'save the document
    'Me.Worksheets.Save
     
    End Sub
    Last edited by RoryA; Nov 14th, 2014 at 08:25 AM.

  9. #29
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by joeyc123 View Post
    Hi Doc,

    I came across another problem this time i am creating a Leave Tracker for my Team. The code below allows everyone to update the excel sheet and sends an email to our team email automatically in outlook however i want the code to also say in the email (MAYBE MSG BODY) which cell was updated and what's the content of that cell e.g. "Joey VL" for joey Vacation Leave or Joey SL for sick leave so everyone in the team know who is on leave on that particular day. Can you help me with this please?
    ........
    . Sorry joeyc. I took a good look, but this one is way above me. I am just now learning Excel VBA and I have no experience with Email or Outlook.

    . Maybe someone else can help?

    . Actually joeyc as it is a comletely new Problem it might be best to start a new Thrread.

    Good Luck

    Alan

  10. #30
    New Member
    Join Date
    Nov 2013
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by waxsublime View Post
    Thanks mirabeau. That macro is a LOT faster. But it's giving me an error on the name for the sheet:

    Runtime 1004:
    Name can't exceed 31 char
    name cannot contain characters: : /\?*
    You did not leave the name blank

    The other macro didn't give the error, but I also haven't checked to see if it did everything correctly. Anyway, thanks again!

    These VBA codes seem just what I need but the name of the data I want to split contains "/". My question is, therefore, is it possible to split data with "/" in the name? or would I need to rename the cells first. If so could someone help me with what the code would be and where it would appear within the code used to split the data into multiple worksheets.

    I am new to VBA and am still trying to understand it, so any help would be appreciated.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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