Help with copy auto filtered data into a new workbook

here is my code, where I am facing an issue. I need to copy 3 sheets from 3 files, in one I have to filter data based on name of sheet from another workbook. I've stucked with copy filtered data to a new workbook. before that all works fine.
Sub Click()
Dim xRow As Long
Dim wbnew, wb1, wb2, wb3, wb4 As Workbook
Dim sht, Data As Worksheet
Dim sh1, sh2, subject, body, emailto, Filter As String
Dim Name As String
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

'openin files to work with

Workbooks.Open filename:="C:\Users\File1.xlsx", ReadOnly:=True
Workbooks.Open filename:="C:\Users\File2.xlsx", ReadOnly:=True, UpdateLinks:=0
Workbooks.Open filename:="C:\Users\File3.xlsx", ReadOnly:=True, UpdateLinks:=0
Workbooks.Open filename:="C:\Users\File4.xlsx", ReadOnly:=True

wb1 = "File1.xlsx"
wb2 = "File2.xlsx"
Set wb3 = Workbooks("File3.xlsx")

'here I create a temporary file
Set wbnew = Workbooks.Add
ActiveSheet.Name = "Data"

'defining columns I will work with

sh1 = wb3.ActiveSheet.Range("A" & i).Value
sh2 = wb3.ActiveSheet.Range("B" & i).Value
Name = wb3.ActiveSheet.Range("F" & i).Value
Filter = wb3.ActiveSheet.Range("C" & i).Value
emailto = wb3.ActiveSheet.Range("E" & i).Value
subject = wb3.ActiveSheet.Range("G" & i).Value
body = wb3.ActiveSheet.Range("I" & i).Value

'main goal is to copy data from 3 different files to new workbook. Below starting with copying data

Workbooks(wb1).Worksheets(sh1).Copy _
Before:=wbnew.Sheets(1)
Workbooks(wb2).Worksheets(sh2).Copy _
Before:=wbnew.Sheets(2)
'from third file I have to autofilter data for column U in File4.xlsx with criteria from File3.xlsx defined above

Set wb4 = Workbooks("File4.xlsx")
wb4.Activate
xRow = wb4.Worksheets("Transactions").Range("A1").End(xlDown).Row
wb4.Worksheets("Transactions").AutoFilterMode = False

xRow = wb4.Worksheets("Transactions").Range("A1").End(xlDown).Row
wb4.Worksheets("Transactions").Range("A:U").AutoFilter Field:=21, Criteria1:=Filter, Operator:=xlFilterValues

'try to copy result from autofilter to new workbook to have 3 new sheets, but having an error, also I tried range copy without success

Workbooks(wb4).ActiveSheet.Range("A1:U" & xRow).SpecialCells(xlCellTypeVisible).Copy _
Destination:=wbnew.Sheets("Data")

wb4.Worksheets("Transactions").AutoFilterMode = False
End Sub
I appreciate your advice. Thank you

Hi,
When posting code, please wrap it with code tags ( Edit code - select code - click the #button.)
It keeps the macro's structure and makes it easy to copy and handle.
Thank you

Hope now it is better. Thank you in advance for help.

.
I don't have your workbooks to experiment with ... so this may not work.

Try editing this line :


... to this :

jayage said:

Hope now it is better. Thank you in advance for help.

Click to expand...

Not really, added the tags myself. See the difference?