Results 1 to 3 of 3

Filter the Range, COPY it and Paste in another sheet,

Threaded View

  1. #1
    Registered User
    Join Date
    01-20-2020
    Location
    ruh
    MS-Off Ver
    window
    Posts
    5

    Filter the Range, COPY it and Paste in another sheet,

    Hi

    I want to autofilter the data and then copy this dynamic range and paste it to another sheet, however, I have an issue which is after filtering the data, the MACRO is copying the unfiltered data and leaves the filtered ones (which they are the one that I need to copy).

    here is my code:

    Sub cont()
    
        Sheets("Items").Select
            
    'To Identify if there is a Range or it is an empty one
        Dim FF As Range
        Dim BB As Integer
        Set FF = Range(Range("d1"), Range("d1").End(xlDown))
        FF.autofilter field:=4, Criteria1:="221311"
        BB = WorksheetFunction.Count(FF.Cells.SpecialCells(xlCellTypeVisible))
        If BB < 1 Then
    'if no Data
        MsgBox "No data available"
        Sheets("Items").Select
        ActiveSheet.ShowAllData
        Sheets("Main").Select
        Exit Sub
        
                            Else
                            
    'Now all the data are available, LETS COPY
    
        Set rng = ActiveSheet.autofilter.Range
        rng.Offset(3, 0).Resize(rng.Rows.Count - 3).Copy
                   
    'first will define the empty cell
        Sheets("Analysis Basis").Select
        Dim lastrow As Long
        lastrow = Range("a1000000").End(xlUp).Row
        Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'second will paste
        ActiveSheet.Paste
        
    'TO remove all filters
        Sheets("Items").Select
        ActiveSheet.ShowAllData
        Sheets("Main").Select
    
        End If
    
    End Sub
    Last edited by AliGW; 01-21-2020 at 06:41 AM. Reason: Irrelevant section of title removed: this is a HELP forum!!!

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filter range, copy range (not entire column), paste in another worksheet
    By LukeNZ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2018, 08:28 AM
  2. Filter range and copy selected column visible data and paste other sheet
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2017, 11:37 AM
  3. VBA to filter, copy and paste in new sheet
    By saravanan1981 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-23-2016, 10:01 PM
  4. [SOLVED] Filter based on Range and copy paste
    By kannan1847 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-31-2015, 07:06 AM
  5. Filter and Copy & Paste it to te last row of the other sheet
    By siobeh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2013, 09:56 AM
  6. Filter Named range-then copy and paste
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-08-2013, 03:41 PM
  7. [SOLVED] Range Selection up to last visible adjacent cell,filter,copy paste to next sheet
    By kcube17 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2013, 07:08 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