Filter Data and copy the result from one workbook to another using VBA

Question

I have 2 workbooks Say Workbook 1 and workbook 2. Need to put a filter in Workbook 2 with filter "INTDN" in column G and copy column "O" and column "J" in workbook 1, column "B" and Column "I" respectively.

Can any one give a VBA code for this?

Workbook and worksheet name can be anything depending on the source however format will be same always.

Some more information for your reference:-

Workbook 2 Put a filter in Row 12 column G :- "INTDN"

Workbook 1 Paste copied data from column "O" to cell B25 downwards. Paste copied data from column "J" to cell I25 downwards.

I will then assign this Macro to every worksheet I need this to work.

I am a novice in VBA. Appreciate your assistance.

This is what I could write:-

Sub CopyData()
'
' CopyData Macro
'

'
    Windows("Book1 (8).xlsx").Activate
    Range("A12").Select
    Selection.AutoFilter
    Range("G12").Select
    ActiveSheet.Range("$A$12:$AV$72").AutoFilter Field:=7, Criteria1:="INTDV"
    Range("O35").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("DebitNotes.xlsm").Activate
    ActiveSheet.Paste
    Range("I25").Select
    Windows("Book1 (8).xlsx").Activate
    Range("J35").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("J35:J72").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DebitNotes.xlsm").Activate
    ActiveSheet.Paste
End Sub

Stackoverflow isn't a code writing service, you need to do some research and have a go at writing some of your own code. Once you've had a go (and likely got stuck or confused, which is normal) you can post up your code and others will be more likely to offer help as it shows you've tried to solve your problem yourself. — James C.
You should be able to record a macro to give yourself a starting point. — bobajob
@JamesC. I have modified my question to add a code. Not sure how logical it is. Appreciate your inputs. — miratshah

Ian Ian · Accepted Answer · 2016-11-30T09:34:42

This code will copy values from column O on sheet1 and copy to end of column P in sheet2. I also made it generic so it can be used for any columns and sheets.

Sub Test()
    Call CopyColumn("Sheet1", "O", "Sheet2", "P") 
End Sub

Function CopyColumn(sourceSheetName As String, sourceColIndex As String, destSheetName As String, destColIndex As String)
    Dim lastRowSource As Integer: lastRowSource = Sheets(sourceSheetName).Cells(Rows.Count, sourceColIndex).End(xlUp).Row
    Dim lastRowDest As Integer: lastRowDest = Sheets(destSheetName).Cells(Rows.Count, destColIndex).End(xlUp).Row
    Sheets(sourceSheetName).Range(sourceColIndex & "1:" & sourceColIndex & lastRowSource).Copy Destination:=Sheets(destSheetName).Range(destColIndex & lastRowDest + 1)
End Function

Filter Data and copy the result from one workbook to another using VBA

2 Answers