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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,807
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,807
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,656
Messages
5,573,418
Members
412,529
Latest member
cTatch
Top