Using Autofilter to find data

**The_Jay_Jay** · 07-27-2007, 11:37 AM

Hi all,

I have a macro that i'm working on that when finished will go into a workbook, select a sheet called "Master", then filter the data in column A by whatever value was entered in a textbox, copy from A to X to another central workbook and repeat for all the workbooks in the folder. At the moment I'm just trying to get it to work on one workbook before setting up the loop, but the problem i'm facing is that when the data is copied and pasted onto the central workbook, there is nothing but blank cells...?

Here is what i have so far...

Sub UpdateMainTracker()
    Dim AppDate As Range
    Set AppDate = ActiveSheet.Range("A1")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Workbooks.Open Filename:="F:\New Tracker\Tracker_Annabelle_Davies.xls", Notify:=False
    Sheets("Master").Select
    ActiveSheet.Unprotect Password:="*********"
    Columns("A:U").Select
    Selection.AutoFilter
    Range("A1").Select
    Selection.AutoFilter Field:=1, Criteria1:=AppDate
    Range("A2:X50").Select
    Selection.Copy
    Workbooks.Open Filename:="F:\Daily Tracker\DummyTracker.xls"
    Windows("DummyTracker.xls").Activate
    Sheets("Jul").Select
    Range("A2").Select
    ActiveSheet.Paste
    Windows("Tracker_Annabelle_Davies.xls").Activate
    ActiveWindow.Close
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

I've set it to copy down to row 50 because there should never be anymore than this. I have also been having problems getting the autofilter to filter by the value in the textbox so I have changed it to filter by the value in A1 for now.

If anyone could help me with regards to filtering by the value in the text box then copying this to the "Dummy Tracker" that would be great as my PC is about to go out of the window (which might anoy my boss!!).

Thanks

Jay

**royUK** · 07-27-2007, 12:36 PM

Your code is full of errors. i am surprised you are not getting alerts just when typing it! for instance Appdate is declared as a Range & yet should be a Date or String. Test this 7 get back to me

Option Explicit

Sub UpdateMainTracker()
    Dim wb     As Workbook
    Dim wbTo   As Workbook
    Dim rFilter As Range
    Dim AppDate As Date

    AppDate = ActiveSheet.Range("A1").Value
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False

        Set wb = Workbooks.Open(Filename:="F:\New Tracker\Tracker_Annabelle_Davies.xls", Notify:=False)
        Set wbTo = Workbooks.Open(Filename:="F:\Daily Tracker\DummyTracker.xls")
        With wb.Sheets("Master")
            .Unprotect Password:="*********"
            If Not .AutoFilterMode Then .Range("A1").AutoFilter
            .Range("A1").AutoFilter Field:=1, Criteria1:=AppDate
            .AutoFilter.Range.Copy wbTo.Sheets("Jul").Range("A2")
            wbTo.Close True
        End With

        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

**The_Jay_Jay** · 08-08-2007, 11:15 AM

thanks for getting back to me RoyUK. That does work but it's copying over blank cells apart from the column headers and it's pasting them at row 2. is that a problem with the way i'm typing the date in the combo box or the way the date is stored on Annabelle's workbook? i only want to copy the data not the headers really. also how can i get this to repeat the steps for around 20 or 30 different people's workbooks?

Using Autofilter to find data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Using Autofilter to find data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions