Results 1 to 5 of 5

Copying Filtered Data From 2 Sheets of One Workbook to Another Isn't Working Correctly

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-04-2010
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    176

    Copying Filtered Data From 2 Sheets of One Workbook to Another Isn't Working Correctly

    Hi all,

    this is the code that copies filtered data from on worksheet in a workbook to another sheet in another workbook (I have attached both dummy workbooks)
    Private Sub GoButton1_Click() 'This part copies the ATTENDANCE data to the report
    Application.ScreenUpdating = False
    Dim MyAttend As Long
    Dim OriginalRow As Long
    Dim TransRow As Long
    Dim Rws As Long, Rng As Range, Strt As Variant, Fnsh As Variant
        Dim Sht1 As Worksheet
        Set Sht1 = Worksheets("Attendances")
    With Sht1
        With .Sort
          With .SortFields
            .Clear
            .Add Key:=Sht1.Range("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=Sht1.Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
          End With
            .SetRange Sht1.Range("A:F")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
            Rws = .Cells(.Rows.Count, "A").End(xlUp).Row
            Strt = Format(ListBox1, "dd-mmm")
            Fnsh = Format(ListBox2, "dd-mmm")
            With .Range(.Cells(1, 1), .Cells(Rws, 6))
               .AutoFilter
                .AutoFilter Field:=2, Criteria1:=">=" & Strt, Operator:=xlAnd, Criteria2:="<=" & Fnsh
            End With
            Set Rng = .AutoFilter.Range
            OriginalRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            MyAttend = .Range("A2:A" & OriginalRow).SpecialCells(xlCellTypeVisible).Count
                If MyAttend <> 1 Then
                        TransRow = Workbooks("Dummy-Report.xlsm").Sheets("TransAttend").Range("A65536").End(xlUp).Row + 1
                        Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, 6).Copy
                        Workbooks("Dummy-Report.xlsm").Sheets("TransAttend").Cells(TransRow, 1).PasteSpecial xlValue
                        Application.CutCopyMode = xlCopy
                End If
            Rng.AutoFilter
        End With
        Application.ScreenUpdating = True
        Unload Me
    End Sub
    This part works fine - now here is the second piece of code which, while similar, should copy different filtered data from a different worksheet to another worksheet in the report. It doesn't. When I run this code it just seems to copy the data from the first code all over again and I can't see why.
    Private Sub GoButton2_Click()
    Application.ScreenUpdating = False
    Dim MyEvals As Long
    Dim FirstRow As Long
    Dim NewRow As Long
    Dim Rws As Long, Rng As Range, Strt As Variant, Fnsh As Variant
        Dim ThisSheet As Worksheet
        Set ThisSheet = Worksheets("Evaluations")
    With ThisSheet
        With .Sort
          With .SortFields
            .Clear
            .Add Key:=ThisSheet.Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=ThisSheet.Range("D:D"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
          End With
            .SetRange ThisSheet.Range("A:X")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
            Rws = .Cells(.Rows.Count, "A").End(xlUp).Row
            Strt = Format(ListBox1, "dd-mmm")
            Fnsh = Format(ListBox2, "dd-mmm")
            With .Range(.Cells(1, 1), .Cells(Rws, 24))
               .AutoFilter
                .AutoFilter Field:=1, Criteria1:=">=" & Strt, Operator:=xlAnd, Criteria2:="<=" & Fnsh
            End With
            Set Rng = .AutoFilter.Range
            FirstRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            MyEvals = .Range("A2:A" & FirstRow).SpecialCells(xlCellTypeVisible).Count
                If MyEvals <> 1 Then
                        NewRow = Workbooks("Dummy-Report.xlsm").Sheets("TransEval").Range("A65536").End(xlUp).Row + 1
                        Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, 24).Copy
                        Workbooks("Dummy-Report.xlsm").Sheets("TransEval").Cells(NewRow, 1).PasteSpecial xlValue
                        Application.CutCopyMode = xlCopy
                End If
            Rng.AutoFilter
        End With
        Application.ScreenUpdating = True
        Unload Me
    End Sub
    Can anyone spot what I'm doing wrong? My plan was to have just the one piece of code to do both but I'm stuck with being able to combine the two if I can't get them to run properly on their own.

    Any help would be most appreciated.

    Cheers,

    AJ
    Attached Files Attached Files
    Last edited by ScotyB; 11-13-2012 at 06:31 PM. Reason: To mark as SOLVED
    Always grateful for the help here - thanks.

Thread Information

Users Browsing this Thread

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

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