Copy data using advanced filter from another workbook.

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
55
Office Version
  1. 2013
Platform
  1. Windows
The below code tries to copy the filtered data from another workbook and pastes it into this workbook.

Problem: Expected end of statement error is shown by highlighting the word ACTION.

I have no clue why is this error appearing. Could anyone please help me?

VBA Code:
Sub Import_Data()

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim x As Integer
Dim lcol, lrow As Long

Application.ScreenUpdating = False


FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & import range", Filefilter:="Excel Files(.xls),xls")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
x = ThisWorkbook.Worksheets(10).Range("B4").Value


With OpenBook.Worksheets(x)

lcol = .Cells(7, .Columns.Count).End(xlToLeft).Column

lrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row


With .Range(.Cells(7, 1), .Cells(lrow, lcol)).AdvancedFilter  Action:=xlfiltercopy,
     Criteriarange:= thisworkbook.worksheets(10).range("C1:G2"),Copytorange:= thisworkbook.Worksheets(10).range("I1"), unique:= False
End With



End With




OpenBook.Close False
End If




Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
You are missing the line continuation after the comma. You need to add a space & underscore
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
You also need to get rid of the With & End With
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,118,765
Messages
5,574,108
Members
412,571
Latest member
Ventura7
Top