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
Bookmarks