CODEWITHSUNDEEP
vbaexcel
user1717622
user1717622

Reputation: 323

VBA: Filter, copy values from external workbook, and past into active workbook

I am looking for a simple solution to this problem.

the code below is my attempt, is the filtering portion for the source workbook correct in its approach?

Also, I want to insure the sourceworkbook is left was it was upon filtering and copying

Many thanks for any ideas!

Sub CopyFilteredValuesToActiveWorkbook()

    Dim sourceValues As Range, targetValues As Range

    Set sourceValues = Workbooks("\\Linkstation\rrm\X_DO_NOT_TOUCH_CC\MasterLogFile\Masterlogfile.xlsx").Worksheets("LogData").Columns("A:Z") 


    ActiveSheet.Range("$A$1:$H$3").AutoFilter Field:=3, Criteria1:="Opera" 


    Set targetValues = Workbooks("\\Linkstation\rrm\Campaign Creator\RAW DATA GENERATOR OPERA.xlsm").Worksheets("MLF").Columns("A:Z")             

    sourceValues.Copy Destination:=targetValues

End Sub

Upvotes: 0

Views: 5841

Answers (1)

MattCrum
MattCrum

Reputation: 1110

Try something like this - defining the workbooks / sheets as variables makes it a lot easier to manage everything. I assumed you'd want the macro to open the other workbook for you - if not, the code can be altered easily.

Sub CopyFilteredValuesToActiveWorkbook()

Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet
Dim rngSource As Range, rngDest As Range

Set wbSource = Workbooks.Open("\\Linkstation\rrm\X_DO_NOT_TOUCH_CC\MasterLogFile\Masterlogfile.xlsx", , True) 'Readonly = True
Set wsSource = wbSource.Worksheets("LogData")
wsSource.Range("$A$1:$H$3").AutoFilter Field:=3, Criteria1:="Opera"
Set rngSource = wsSource.Range("A:Z")

Set wbDest = ThisWorkbook
Set wsDest = wbDest.Worksheets("MLF")
Set rngDest = wsDest.Range("A:Z")

rngDest.Value = rngSource.Value 'Copies values over only, if you need formatting etc we'll need to use something else

wbSource.Close (False) 'Close without saving changes

End Sub

Upvotes: 1

Related Questions