Results 1 to 5 of 5

Thread: Help with copy auto filtered data into a new workbook

  1. #1
    Neophyte jayage's Avatar
    Join Date
    Dec 2018
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Help with copy auto filtered data into a new workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,675
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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
    Thank you Ken for this secure forum.

  3. #3
    Neophyte jayage's Avatar
    Join Date
    Dec 2018
    Posts
    2
    Articles
    0
    Excel Version
    2016
    Hope now it is better. Thank you in advance for help.

    Code:
    Sub Click()
    Dim xRow As Long
    Dim wbnew, wb1, wb2, wb3, wb4 As Workbook
    Dim sht, Data As Worksheet
    Dim sh1, sh2, Filter As String
    '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"
    'and definition of columns I will work with
        sh1 = wb3.ActiveSheet.Range("A" & i).Value
        sh2 = wb3.ActiveSheet.Range("B" & i).Value
        Filter = wb3.ActiveSheet.Range("C" & 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
    'Autofilter
        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")
    End Sub
    Last edited by Pecoflyer; 2018-12-22 at 08:17 AM. Reason: Added code tags

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    225
    Articles
    0
    Excel Version
    2007
    .
    I don't have your workbooks to experiment with ... so this may not work.

    Try editing this line :

    Code:
    Workbooks(wb4).ActiveSheet.Range("A1:U" & xRow).SpecialCells(xlCellTypeVisible).Copy
    ... to this :

    Code:
    wb4.ActiveSheet.Range("A1:U" & xRow).SpecialCells(xlCellTypeVisible).Copy

  5. #5
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,675
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Quote Originally Posted by jayage View Post
    Hope now it is better. Thank you in advance for help.
    Not really, added the tags myself. See the difference?
    Thank you Ken for this secure forum.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •