Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Excel 2013 Select data by date and move to new sheet

    I'm a newbie to this forum and to Excel VBA. I'm retired but interested in learning Excel. Been studying it for about 5 months. I need to learn enough VBA to do work the built in functions won't do. I purchased Excel 2010 Power Programming with VBA by Walkenbach yesterday. I choose 2010 over 2013 because it had a CD Rom. I thought they was an advantage, but don't know for sure.

    Question: I don't know of a way to select data by dates and move it to a new sheet in the same workbook. I have a file with several thousand lines of data and would like to use a macro to group and move data my months to sheets with the months of the year as names.

    Jan Feb March and etc.

    The dates are formatted as 4/8/2015

    Thanks in advance for any help.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Excelnewbie,

    Welcome to the Lounge as a NEW Poster!

    Here's some code that should do what you are asking.
    Code:
    Option Explicit
    
    Sub DistributeByDate()
    
       Dim lLastDataRow         As Long
       Dim lCurRow              As Long
       Dim shtDest(1 To 12)     As Worksheet
       Dim lShtLastRow(1 To 12) As Long
       Dim iCurMonth            As Integer
       
       
       Set shtDest(1) = Sheets("Jan")
       Set shtDest(2) = Sheets("Feb")
       Set shtDest(3) = Sheets("Mar")
    '   Set shtDest(4) = Sheets("Apr") '*** Commented out for TESTING ONLY! ***
    '   Set shtDest(5) = Sheets("May")
    '   Set shtDest(6) = Sheets("Jun")
    '   Set shtDest(7) = Sheets("Jul")
    '   Set shtDest(8) = Sheets("Aug")
    '   Set shtDest(9) = Sheets("Sep")
    '   Set shtDest(10) = Sheets("Oct")
    '   Set shtDest(11) = Sheets("Nov")
    '   Set shtDest(12) = Sheets("Dec")
       
       For lCurRow = 1 To 12
          lShtLastRow(lCurRow) = 2
       Next lCurRow
       
       lLastDataRow = Range("a1").Offset(Rows.Count - 1, 0).End(xlUp).Row()
       
       For lCurRow = lLastDataRow To 2 Step -1
       
          iCurMonth = Month(Cells(lCurRow, 1))
          Cells(lCurRow, 1).EntireRow.Copy _
                   Destination:=shtDest(iCurMonth).Cells(lShtLastRow(iCurMonth), 1)
          lShtLastRow(iCurMonth) = lShtLastRow(iCurMonth) + 1 '*** Increment last row counter ***
          
       Next lCurRow
       
    End Sub     'DistributeByDate
    Here's the test file: ExcelNewbie.xlsm

    Note: the code copies the records in reverse order! This can of course be changed but if you also want to delete the records from the Master sheet you need to do it in this order for it to work. Post back if you need to change things.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG,

    Thank you! This will solve a problem and provide VBA tutoring on top of it.

    Thanks for the offer to follow up. I'll study this and get back with you if needed.

    I can tell it will take awhile to learn VBA.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Excel,

    Since I was working on a solution, I will post mine as well. This code assumes that the sheet with the data is the first sheet while the remaining 12 sheet are the months of the year. To make the code generic, you can change the values for StartRow and ColNums to customize it to your specific application. StartRow is the first starting row of the data on your first sheet. ColNums is the number of columns you want copied. So if your largest record has 10 columns of data, change the code to 10.

    The code will look at the date and extract the month which tells it which sheet to write to. It will the append the record to the next available row on that sheet. and can accommodate blank cells and rows

    HTH,
    Maud

    Code:
    Public Sub MoveData()
    On Error Resume Next
    Dim Mnth As Integer, StartRow As Integer
    StartRow = 2 'CHANGE TO THE STARTING ROW ON DATA SHEET
    ColNums = 8 'CHANGE TO THE LARGEST NUMBER OF COLUMNS USED
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = StartRow To LastRow
        Mnth = Month(Cells(I, 1))
        With Worksheets(Mnth + 1)
        NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        For J = 1 To ColNums
            .Cells(NextRow, J) = Cells(I, J)
        Next J
        End With
    Next I
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG,

    I've been working with this and added some data. For some reason I'm getting an error. It worked fine before I added data.

    See attached file.
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    E.N.

    You need to add the sheets for the other months since you've included data for them then you need to UNcomment the lines in the macro that are marked as commented for testing. I did this to your file and it works fine. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    Maudibe,

    Thank you for your help. The code you provided did the job. It moved hundreds of rows of data to the proper place.

    The only problem I had was that I had to remove the Option Explicit heading in the module.

    I'm just starting out with VBA. The code you provided will give me something to learn with.

    Thank you for everything.
    Last edited by Excelnewbie; 2015-04-08 at 20:37.

  8. #8
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG,

    Wow! That is great. I did as you said and it worked like a charm.

    Thanks again for your help.

Posting Permissions

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