+ Reply to Thread
Results 1 to 2 of 2

(VBA) Trying to transfer filtered data to other worksheet starting from second row

Hybrid View

  1. #1
    Registered User
    Join Date
    11-23-2009
    Location
    singapore
    MS-Off Ver
    Excel 2016
    Posts
    10

    (VBA) Trying to transfer filtered data to other worksheet starting from second row

    I am trying to use VBA to transfer filtered result from sheet 1. Beased on the result filtered, copy them into sheet 2 - the first row which is empty.


    Dim rng As Range
    Dim rngtocopy As Range
    Dim autofiltrng As Range
    
    Dim MyArray(1 To 3) As String
    MyArray(1) = "*a*"
    MyArray(2) = "*b*"
    MyArray(3) = "*c*"
    
    'specify sheet name in which the data is stored
    Worksheets("Raw Data").Select
    ActiveSheet.Range("A1").Select
    
    counti = 0
    For documentsfilter = 1 To UBound(MyArray)
    counti = counti + 1
    
    If counti > 3 Then
        Exit For
    Else
        Sheets("Raw Data").Range("A1").AutoFilter field:=7, Criteria1:=MyArray(documentsfilter)
        
        checkempty = Application.WorksheetFunction.Subtotal(3, Range("A2:A100000"))
    
        If checkempty = 0 Then
            MsgBox "No Data " + MyArray(documentsfilter)
        Else
            
            With ActiveSheet.AutoFilter.Range
            Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            Set rng = Sheets("Raw Data").autofiltrng
            rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
            End With
            
            
            
        End If
        
        Worksheets("Raw Data").Select
        ActiveSheet.Range("A1").Select
        ActiveSheet.ShowAllData
            
    End If
    
    Next documentsfilter
        
    End Sub
    i am stuck at these set of code which i have no idea how to fix it.
            With ActiveSheet.AutoFilter.Range
            Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            Set rng = Sheets("Raw Data").autofiltrng
            rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
    encountered error 1004 for debug
    Last edited by jackfood; 05-26-2020 at 06:26 AM.

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: (VBA) Trying to transfer filtered data to other worksheet starting from second row

    Hi, What if you change your code into this:
                    With ActiveSheet.AutoFilter.Range
                        Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
                        autofiltrng.CurrentRegion.Offset(1, 0).Copy Worksheets("Sheet2").Range("A" & Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1)
                    End With
    Cheers
    Erwin
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-12-2017, 01:02 PM
  2. Code to copy and paste a data set into a different worksheet starting last row
    By hawk77EF in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2015, 11:13 AM
  3. Replies: 5
    Last Post: 09-30-2014, 07:00 PM
  4. [SOLVED] Transfer Userform data on 1 worksheet to different worksheet (empty row = wrong sheet)
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2014, 10:08 AM
  5. Need Help Creating A Macros To Transfer Data From Worksheet to Worksheet
    By rodtay5283 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-27-2014, 02:11 AM
  6. [SOLVED] Use a button to transfer filtered data to destination cell
    By Consty1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-09-2013, 09:46 AM
  7. Replies: 4
    Last Post: 01-18-2013, 05:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1