I am trying to use VBA to transfer filtered result from sheet 1. Beased on the result filtered, copy them into sheet 2 - the first row which is empty.
Dim rng As Range
Dim rngtocopy As Range
Dim autofiltrng As Range
Dim MyArray(1 To 3) As String
MyArray(1) = "*a*"
MyArray(2) = "*b*"
MyArray(3) = "*c*"
'specify sheet name in which the data is stored
Worksheets("Raw Data").Select
ActiveSheet.Range("A1").Select
counti = 0
For documentsfilter = 1 To UBound(MyArray)
counti = counti + 1
If counti > 3 Then
Exit For
Else
Sheets("Raw Data").Range("A1").AutoFilter field:=7, Criteria1:=MyArray(documentsfilter)
checkempty = Application.WorksheetFunction.Subtotal(3, Range("A2:A100000"))
If checkempty = 0 Then
MsgBox "No Data " + MyArray(documentsfilter)
Else
With ActiveSheet.AutoFilter.Range
Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
Set rng = Sheets("Raw Data").autofiltrng
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
End With
End If
Worksheets("Raw Data").Select
ActiveSheet.Range("A1").Select
ActiveSheet.ShowAllData
End If
Next documentsfilter
End Sub
i am stuck at these set of code which i have no idea how to fix it.
With ActiveSheet.AutoFilter.Range
Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
Set rng = Sheets("Raw Data").autofiltrng
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
encountered error 1004 for debug
Bookmarks