Filter data in multiple sheets (same workbook) and copy data in sheet of another workbook

Question

I have a workbook which contains multiple sheets with some data with same structure. I need to apply filter to Sales columns for June value in every sheet. Then copy filters data to another workbook in single sheet. Filter first sheet in datasource June.xlsm workbook, paste filtered data to name sheet in result.xlsx workbook, then filter second sheet in datasource June.xlsm and paste data in name sheet in result.xlsx . My codes work well till filter and copy but gives error

Object required at the paste line ActiveSheet.Cells(Row.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues.

I know i am missing bit of trick with my logic but not able to figure it out since couple of hours and its getting bit frustrating. Please guide me to get over it. Regards

Sub FilterAll()

    Dim num As Integer
    Dim rngFound As Range
    Dim myCol As Long

    Dim wsData As Workbook
    Dim destData As Workbook
    Dim LastRow As Long
    Dim lastCol As Long
    Dim copyCol As Long
    Dim sPath As String

    Application.Workbooks("datasource JUNE.xlsm").Activate

    For Each Sheet In ActiveWorkbook.Sheets

        num = num + 1

        Sheet.Activate
        LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

        Set rngFound = ActiveSheet.Rows(1).Find(What:="", LookIn:=xlValues, LookAt:=xlWhole, _
                                                SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

        lastCol = rngFound.Column - 1            ' this will give last used column: use in autofilter synatx

        Set rngFound = ActiveSheet.Rows(1).Find(What:="*Sales*", LookIn:=xlValues, LookAt:=xlWhole, _
                                                SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        myCol = rngFound.Column

        ActiveSheet.Range(Cells(1, 1), Cells(LastRow, lastCol)).AutoFilter Field:=myCol, Criteria1:="*June*"

        Application.ActiveSheet.UsedRange.Offset(1, 0).Copy ' usedrange to select only used cells
        'Selection.Copy
        sPath = Application.ActiveWorkbook.Path
        Set destData = Workbooks.Open(sPath & "\result.xlsx")

        MsgBox "result opens"

        Application.Workbooks("result.xlsx").Worksheets("name").Activate
        MsgBox ActiveSheet.Name
        ActiveSheet.Cells(Row.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Application.CutCopyMode = False


    Next

    MsgBox num

End Sub

Thanks for correction. But still I am getting error at 'ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues' Paste special of Range class failed. — D. Ace

D. Ace D. Ace · Accepted Answer · 2018-07-03T14:27:07

changed the logic of copy and paste to vintage syntax and it worked perfectly.Here is the line of code which was required:

Application.Workbooks("datasource JUNE.xlsm").ActiveSheet.UsedRange.Copy _
Destination:=Workbooks("result.xlsx").Worksheets(1).Range("A" & LastRow)

This Range("A" & LastRow) had to be written correctly.

Filter data in multiple sheets (same workbook) and copy data in sheet of another workbook

1 Answers