Msgbox to give the number of new rows copied or count and give the new entries in one code execution.

[pedie]

I have to this code that copy and paste for eg: after filtering.
So i want to ask if there is way to count number of new rows copied and pasted

I sure this will be easy for you guys!

Thanks Pedie

Sub Macro2()
Sheet1.Range("A1:B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$24").AutoFilter Field:=2, Criteria1:="N"
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFilter
End Sub

 

[JackDanIce]

Try:

Sub Macro2()
Dim i as Long
Dim RangeAcross as Range
    Sheet1.Range("A1:B1").AutoFilter
    ActiveSheet.Range("$A$1:$B$24").AutoFilter Field:=2, Criteria1:="N"
    set RangeAcross = Range(Range("A3"), Range("A3").End(xlToRight))
    i = Range("A3").End(xlDown).Row
    Range(RangeAcross, Range("A" & i)).Copy
    Range("D3").PasteSpecial Paste:=xlPasteValues
    Selection.AutoFilter
    MsgBox i & " Rows have been copied and pasted"
End Sub

 

[pedie]

Hi Jack, when i run this code it is count the whole table row instead of special row (row that were filetered to be copied)...it is giving result as 22 but the rows copied were only 7 rows when filtered with "N"..

Could you pls check it again???

 

[pedie]

Anyone?

 

[pedie]

I think i found it: thanks anyways. Will get back latter... But if anyone has anything to share then please do.
I used to count before being copied....

intCount = WorksheetFunction.Subtotal(3, Range("A2:A10000"))

 

[VoG]

Perhaps

    Application.CutCopyMode = False
    MsgBox Range("A1:A24").SpecialCells(xlCellTypeVisible).Count & "rows copied", vbInformation
    Selection.AutoFilter