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
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