Get Data from different sheets according to criteria

Posted on 2009-02-10
Last Modified: 2013-11-25
I am creating an excell VBA tool that can do the following

In sheet summary: when the location is specified (example: London)
and the starting month is entered (example: Jan)
then the ending month is entered (example: May)

When the user presses 'Get Data', the macro will go to the sheets of the month period
specified (in this case: Jan, Feb, Mar, Apr and May) filter the data from those sheets
in order to copy all the rows that match the location column (Column U) in the month sheets.
Finally all those data must be stored in a new sheet created name (the same as location, in this
case 'London').

I need to be able to change the starting month to May and ending to Aug and get the data for this period.

Thanks for the help
Question by:ZixK
    LVL 59

    Expert Comment

    by:Saurabh Singh Teotia
    There you go your workbook and code for your reference which will do what you want...
    The only change i made which you need to keep in mind is that the start date and end date in the proper date done in the enclosed workbook..and post that it will automatically create worksheet for your search criteria...

    Sub copy()
        Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
        Dim m As Byte, rng As Range, cell As Range
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Set ws1 = Sheets("Summary")
        If ws1.Cells(3, 3).Value = "" Then
            MsgBox "Please enter a search term to search"
            Exit Sub
        End If
        If ws1.Cells(5, 3).Value = "" Then
            MsgBox " Please Enter Start Month"
            Exit Sub
        ElseIf IsDate(ws1.Cells(5, 3).Value) = False Then
            MsgBox " Please Enter a Valid Start Month"
            Exit Sub
        End If
        If ws1.Cells(7, 3).Value = "" Then
            MsgBox " Please Enter End Month"
            Exit Sub
        ElseIf IsDate(ws1.Cells(7, 3).Value) = False Then
            MsgBox " Please Enter a Valid End Month"
            Exit Sub
        End If
        x = ws1.Cells(5, 3).Value
        Sheets(ws1.Cells(3, 3).Value).Delete
        Sheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = ws1.Cells(3, 3).Value
        Set ws2 = ActiveSheet
        For Each ws In ActiveWorkbook.Worksheets
            For m = Month(ws1.Cells(5, 3).Value) - 1 To Month(ws1.Cells(7, 3).Value) - 1
                If Format(DateAdd("m", m, ws1.Cells(5, 3).Value), "mmm") = ws.Name Then
                    ws.Rows(1).copy ws2.Range("A1")
                    Set rng = Range("U2:U" & ws.Cells(65536, "U").End(xlUp).Row)
                    For Each cell In rng
                        If Trim(UCase(cell.Value)) = Trim(UCase(ws1.Cells(3, 3).Value)) Then
                            cell.EntireRow.copy ws2.Range("A" & ws2.Cells(65536, "A").End(xlUp).Row + 1)
                        End If
                    Next cell
                End If
            Next m
        Next ws
        MsgBox "Done"
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub

    Open in new window


    Author Comment

    Thanks but I am unable to run the macro.
    It is returning an error.
    Can you please check why
    LVL 59

    Expert Comment

    by:Saurabh Singh Teotia
    Whats the error message that you are getting..?? and which line it is..?? did you try in the workbook that i uploaded...??

    Author Comment

    Run time error 9
    Subscript out of range
    coming from the sub copy()
    This line:
    '    Sheets(ws1.Cells(3, 3).Value).Delete'
    LVL 59

    Assisted Solution

    by:Saurabh Singh Teotia
    Ahh...just add this one line on line-28 and this will take care of the same...
    On Error Resume Next
    LVL 17

    Accepted Solution

    Here is my version, hope this is what you want:

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now